{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 导包和数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from pyecharts.charts import Bar,Line,Map,Page,Pie  \n",
    "from pyecharts import options as opts \n",
    "from pyecharts.globals import SymbolType  \n",
    "import warnings\n",
    "import seaborn as sns\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "%matplotlib inline\n",
    "sns.set_style('white',{'font.sans-serif':['simhei','Arial']})\n",
    "pd.set_option(\"display.max_column\", None)\n",
    "pd.set_option(\"display.max_row\",None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_user_source = pd.read_excel(\"D:\\\\office\\\\data\\\\data\\\\user_source.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 线索_id 点击广告录入信息的用户"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>广告投放渠道</th>\n",
       "      <th>线索_id</th>\n",
       "      <th>跟进次数</th>\n",
       "      <th>获取时间</th>\n",
       "      <th>状态</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020_8320180</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>微信添加成功</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020_7833328</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>微信添加成功</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020_9392768</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>微信添加成功</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020_3731912</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>微信添加成功</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020_8304303</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>微信添加成功</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      广告投放渠道         线索_id  跟进次数       获取时间      状态\n",
       "0  2020_1001  2020_8320180     2 2020-05-01  微信添加成功\n",
       "1  2020_1001  2020_7833328     2 2020-05-01  微信添加成功\n",
       "2  2020_1001  2020_9392768     1 2020-05-01  微信添加成功\n",
       "3  2020_1001  2020_3731912     1 2020-05-01  微信添加成功\n",
       "4  2020_1001  2020_8304303     2 2020-05-01  微信添加成功"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_user_source.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 13977 entries, 0 to 13976\n",
      "Data columns (total 5 columns):\n",
      " #   Column  Non-Null Count  Dtype         \n",
      "---  ------  --------------  -----         \n",
      " 0   广告投放渠道  13977 non-null  object        \n",
      " 1   线索_id   13977 non-null  object        \n",
      " 2   跟进次数    13977 non-null  int64         \n",
      " 3   获取时间    13977 non-null  datetime64[ns]\n",
      " 4   状态      13977 non-null  object        \n",
      "dtypes: datetime64[ns](1), int64(1), object(3)\n",
      "memory usage: 546.1+ KB\n"
     ]
    }
   ],
   "source": [
    "data_user_source.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 分析目标\n",
    "   * 两个广告渠道数据对比，哪个更优\n",
    "   * 两个月销售数据的环比\n",
    "   * 获得活动赠送一瓶白酒的客户后续表现如何\n",
    "   * 成功添加客户微信的开发率和复购率如何\n",
    "   * 客户下单的价格区间分布和各区间销售额占比\n",
    "   * 获取客户手机号后的通话情况"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "#删除重复点击广告并录入信息的客户\n",
    "data_user_source.drop_duplicates(['线索_id'],inplace = True)\n",
    "#单独提取出月\n",
    "data_user_source['月份'] = data_user_source['获取时间'].dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['微信添加成功', '不同意添加', '未接来电', '接听挂断', '拒接电话', '同意添加微信', '无效电话'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查看状态信息\n",
    "data_user_source['状态'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 各类状态占比以及销售跟进情况"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "添加成功     9951\n",
       "不同意添加    1919\n",
       "未通话      1573\n",
       "接听挂断      372\n",
       "无效电话      118\n",
       "Name: 状态, dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#相同信息合并\n",
    "data_user_source.loc[data_user_source['状态']=='微信添加成功','状态'] = '添加成功'\n",
    "data_user_source.loc[data_user_source['状态']=='同意添加微信','状态'] = '添加成功'\n",
    "data_user_source.loc[data_user_source['状态']=='未接来电','状态'] = '未通话'\n",
    "data_user_source.loc[data_user_source['状态']=='拒接电话','状态'] = '未通话'\n",
    "data_msg = data_user_source['状态'].value_counts()\n",
    "data_msg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<script>\n",
       "    require.config({\n",
       "        paths: {\n",
       "            'echarts':'https://assets.pyecharts.org/assets/echarts.min'\n",
       "        }\n",
       "    });\n",
       "</script>\n",
       "\n",
       "        <div id=\"580959356be94780837518dcab650852\" style=\"width:900px; height:500px;\"></div>\n",
       "\n",
       "<script>\n",
       "        require(['echarts'], function(echarts) {\n",
       "                var chart_580959356be94780837518dcab650852 = echarts.init(\n",
       "                    document.getElementById('580959356be94780837518dcab650852'), 'white', {renderer: 'canvas'});\n",
       "                var option_580959356be94780837518dcab650852 = {\n",
       "    \"animation\": true,\n",
       "    \"animationThreshold\": 2000,\n",
       "    \"animationDuration\": 1000,\n",
       "    \"animationEasing\": \"cubicOut\",\n",
       "    \"animationDelay\": 0,\n",
       "    \"animationDurationUpdate\": 300,\n",
       "    \"animationEasingUpdate\": \"cubicOut\",\n",
       "    \"animationDelayUpdate\": 0,\n",
       "    \"color\": [\n",
       "        \"#EF9050\",\n",
       "        \"#3B7BA9\",\n",
       "        \"#6FB27C\",\n",
       "        \"#FFAF34\",\n",
       "        \"#D8BFD8\",\n",
       "        \"#00BFFF\",\n",
       "        \"#7FFFAA\"\n",
       "    ],\n",
       "    \"series\": [\n",
       "        {\n",
       "            \"type\": \"pie\",\n",
       "            \"clockwise\": true,\n",
       "            \"data\": [\n",
       "                {\n",
       "                    \"name\": \"\\u6dfb\\u52a0\\u6210\\u529f\",\n",
       "                    \"value\": 9951\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u4e0d\\u540c\\u610f\\u6dfb\\u52a0\",\n",
       "                    \"value\": 1919\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u672a\\u901a\\u8bdd\",\n",
       "                    \"value\": 1573\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u63a5\\u542c\\u6302\\u65ad\",\n",
       "                    \"value\": 372\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u65e0\\u6548\\u7535\\u8bdd\",\n",
       "                    \"value\": 118\n",
       "                }\n",
       "            ],\n",
       "            \"radius\": [\n",
       "                \"35%\",\n",
       "                \"60%\"\n",
       "            ],\n",
       "            \"center\": [\n",
       "                \"50%\",\n",
       "                \"50%\"\n",
       "            ],\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8,\n",
       "                \"formatter\": \"{b}:{d}%\"\n",
       "            },\n",
       "            \"rippleEffect\": {\n",
       "                \"show\": true,\n",
       "                \"brushType\": \"stroke\",\n",
       "                \"scale\": 2.5,\n",
       "                \"period\": 4\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"legend\": [\n",
       "        {\n",
       "            \"data\": [\n",
       "                \"\\u6dfb\\u52a0\\u6210\\u529f\",\n",
       "                \"\\u4e0d\\u540c\\u610f\\u6dfb\\u52a0\",\n",
       "                \"\\u672a\\u901a\\u8bdd\",\n",
       "                \"\\u63a5\\u542c\\u6302\\u65ad\",\n",
       "                \"\\u65e0\\u6548\\u7535\\u8bdd\"\n",
       "            ],\n",
       "            \"selected\": {},\n",
       "            \"show\": true,\n",
       "            \"left\": \"2%\",\n",
       "            \"top\": \"15%\",\n",
       "            \"orient\": \"vertical\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10,\n",
       "            \"itemWidth\": 25,\n",
       "            \"itemHeight\": 14\n",
       "        }\n",
       "    ],\n",
       "    \"tooltip\": {\n",
       "        \"show\": true,\n",
       "        \"trigger\": \"item\",\n",
       "        \"triggerOn\": \"mousemove|click\",\n",
       "        \"axisPointer\": {\n",
       "            \"type\": \"line\"\n",
       "        },\n",
       "        \"textStyle\": {\n",
       "            \"fontSize\": 14\n",
       "        },\n",
       "        \"borderWidth\": 0\n",
       "    },\n",
       "    \"title\": [\n",
       "        {\n",
       "            \"text\": \"\\u70b9\\u51fb\\u5e7f\\u544a\\u7528\\u6237\\u8ddf\\u8fdb\\u72b6\\u51b5\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10\n",
       "        }\n",
       "    ]\n",
       "};\n",
       "                chart_580959356be94780837518dcab650852.setOption(option_580959356be94780837518dcab650852);\n",
       "        });\n",
       "    </script>\n"
      ],
      "text/plain": [
       "<pyecharts.render.display.HTML at 0x1b05be57208>"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_pair = [list(z) for z in zip(data_msg.index.tolist(), data_msg.values.tolist())]\n",
    "pie1 = Pie() \n",
    "pie1.add('', data_pair, radius=['35%', '60%'])\n",
    "pie1.set_global_opts(title_opts=opts.TitleOpts(title='点击广告用户跟进状况'), \n",
    "                     legend_opts=opts.LegendOpts(orient='vertical', pos_top='15%', pos_left='2%'))\n",
    "pie1.set_series_opts(label_opts=opts.LabelOpts(formatter=\"{b}:{d}%\"))\n",
    "pie1.set_colors(['#EF9050', '#3B7BA9', '#6FB27C', '#FFAF34', '#D8BFD8', '#00BFFF', '#7FFFAA'])\n",
    "pie1.render_notebook() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x1b05b79cac8>"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAeoAAAEGCAYAAACq69bDAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAAgAElEQVR4nO3deViU9f7/8efNDrKoiai4lbuiuJGpqGV2UtQyTm4QLef4K8s2K1vMU5bZcs7puHQ081vpibA0j8fMLTNzTVHJckHMLBFwQVAQHRGGmd8f6CSBC4jMDPN6XFcX8pn7nnnPp9HXvO+Zz30bVqvVioiIiDgkN3sXICIiIpemoBYREXFgCmoREREHpqAWERFxYApqERERB6agFhERcWAe9njQbt26ERoaao+HFhERsYuMjAwSExPLvZ9dgjo0NJRFixbZ46FFRETsIjo6ukL76dC3iIiIA1NQi4iIODAFtYiIiANTUIuIiDgwBbWIiIgDU1CLiIg4MAW1iIiIA3P6oLZYrExcsof4Lank5RfauxwREZFK5fRBXWixkHz4FH9bvJtb3vyW8f/bxZ7DufYuS0REpFLY5cxklcnbw535j9zCzvRcPt2SyqIf0pmXeIiOjWoS260xgzo0wNfL3d5lioiIVIjTBzWAYRiEN6pJeKOaTBjYlkU70klIPMS4hTuZtDSZe7s0IqZbY5rX9bd3qSIiIuVyxaCeN28eK1asAODUqVOEh4djNps5cOAAffr04bHHHgNg/PjxpcbsIcjPk4d63siDPZqS+NsJEhIPEb/lIB9v+o1bbqpNbLcm3NmuHl4eTn/UX0REXMAV0yomJob4+Hji4+Pp2rUrjRo1wmKxMH/+fNLS0jh48CCrVq0qNWZvhmFwy0038N7ITmx+6XZe6N+ajJyzPPHZDnq8/S1/X5lC2gmTvcsUERG5rKs+9H3s2DGysrIwDIMBAwYAEBkZSVJSEnv37i011rRp0+tScEXU8ffm0Vub8Ujvm1i//zgJiYeYte4A7687QJ+WwcR2a8JtrYLxcFeXLSIijuWqgzohIYGRI0eyZMkSQkJCAAgKCiIjIwOTyVRqzBG5uRnc2qout7aqy5Hcs3y+NY3Ptx3i/32ynfpBPoyIaMyImxsREuhj71JFRESAq1yeZbFYSExMpFu3bvj5+ZGfnw+AyWTCYrGUOebo6gf5MvaOlmx6oS+z7utC87r+TFn9Mz3eXsPo+CQ27D+OxWK1d5kiIuLirqqj3r59Ox06dMAwDMLCwkhKSqJjx46kpKRw4403Uq9evVJjzsLD3Y3+YfXoH1aP1OwzzNt6iC+2p7Nyz1Ga3OBHzM2NGdq1EbVreNm7VBERcUFXFdQbN24kIiICgH79+hETE0NmZibr169nwYIFGIZRaswZNbmhBi8NaMMzd7Rk5e6jJGw5xFsrUnh31c9Eta9H7C1N6NqkFoZh2LtUERFxEYbVai338d3c3Fw2bdpEREQEwcHBlxy7lOjoaBYtWlSxiqvYz8fymJd4iP8mpZN3zkzLEH9iuzXhns6hBPp42rs8ERFxEhXNvgoF9bVypqC+wFRgZulPR/g0MZWd6bn4erpzd8cGxHZrQvuGQfYuT0REHFxFs69anJmsKvh5eTAsohHDIhqxMz2HeYmH+PLHw3y+LY0ODYOI7daYweEN8PPSlIqISOVRqlRAh4Y16dCwJuMHtmHxjgw+3ZLKC//dxRvL9vLnzg2J6daYliEB9i5TRESqAQX1NQj08eT+7k2Ju6UJ21NPkrAllXmJh5j7/UFublqb2Fsa0z+sHt4euiiIiIhUjIK6EhiGQUTT2kQ0rc0rgwtYmJRGQuIhnvr8R2rX8GJo14bE3NyYJjfUsHepIiLiZBTUlax2DS8e7t2MUZE3selAFglbDvHhht/4YN2v9GpRh9huTejXpq5OVyoiIldFQX2duLkZ9GoRTK8WwRw7lW87XenoT5MICfS2na60fpCvvUsVEREHpqCuAiGBPjzVrwVjbmvGd/uOk5CYyvQ1+3lvzX5ubxNCbLfG9G4RjJubTqQiIiIlKairkIe7G3e0DeGOtiEcyjbx2bZDLNiWxjfJx2hU25eYm5swtGtD6vh727tUERFxEPqg1E4a3+DHC/1bs/ml23lvZCdCa/ryzsoUur/1LU98toMtv2Zjh3PRiIiIg1FHbWdeHm4MDm/A4PAG/JKZR8L505V+9dNhmtf1J7ZbY6I7NSTIT6crFRFxReqoHUjzugG8OrgdieP78Y97O+Dv7cFrXyXT7a3VjPviJ35My1GXLSLiYtRROyBfL3eGdm3E0K6N2J2RS0LiIb78MYMvktJp1yCQ2G5NuLtjA2p463+fiEh1p47awYWFBvFWdHsSx9/OpCFhFFmsjP/fLrq9+S1/W7yblKOn7F2iiIhcR2rJnESAjydxtzThvm6N+eFQDglbUpm/PY34Lal0aVKL2G6NiWpfHx9Pna5URKQ6UUftZAzDoEuTWvxreEcSX7qdCQPbcOJMAc8s+Ilb3vqWycuSSc0+Y+8yRUSkkiionVitGl6M6nUTa57tw7xR3ejR7AbmbDrIgGkbyMg5a+/yRESkEiioqwHDMOjRvA4zY7vwzTN9KLJYeWdFir3LEhGRSqCgrmZurFODh3vfxJKfDpOUesLe5YiIyDW66qCeOHEia9asAWD8+PEMHz6cmTNn2m4va0zsY3SfZoQEevP6V8lYLFp3LSLizK4qqLdv305WVhZ9+/Zl1apVWCwW5s+fT1paGgcPHixzTOynhrcHL/RvzU/puSz+McPe5YiIyDW4YlAXFhYyYcIEQkNDWb16NVu3bmXAgAEAREZGkpSUVOaY2NeQjqGEN6rJOytTOHPObO9yRESkgq4Y1IsXL6Z58+aMGjWKXbt2kZCQQEhICABBQUFkZ2djMplKjYl9ubkZvDKoLcdOnWPWugP2LkdERCroikG9d+9ehg0bRnBwMHfddRddu3YlPz8fAJPJhMViwc/Pr9SY2F+XJrW4K7wBs9f/SvpJk73LERGRCrhiUDdu3Ji0tDQAdu3aRUZGhu3QdkpKCqGhoYSFhZUaE8fw4oDWGAa8reVaIiJO6YqnEL333nsZP348y5cvx2w2Ex8fz6OPPkpmZibr169nwYIFGIZBTExMiTFxDA1q+vJI72ZM+3Y/D/Y4Qdemte1dkoiIlINhrcB1E3Nzc9m0aRMREREEBwdfcuxSoqOjWbRoUcUqlnIzFZjp+891BAd48+WYnri5GfYuSUTE5VQ0+yp0wpOgoCCioqJKBHJZY+IY/Lw8eHFAa3Zl5PLfH9LtXY6IiJSDzkzmIu7u2IBOjWvy96/3abmWiIgTUVC7CMMoXq51PO8cM9f+Yu9yRETkKimoXUinxrW4p1Mo/7fhN9JOaLmWiIgzUFC7mOf7t8LdMLRcS0TESSioXUz9IF9G92nGsl1HSPxVZ5ATEXF0CmoX9HDvm2gQ5MPrS5Mp0tW1REQcmoLaBfl6ufPCgNbsOXyK/yZpuZaIiCNTULuou8Ib0Pn8cq3TWq4lIuKwFNQuyjAMXh3cjqzT55jxnZZriYg4KgW1CwtvVJPozqF8tOE3DmVruZaIiCNSULu4F/q3xt3N4K0Ve+1dioiIlEFB7eJCAn147NZmrNh9lC1ariUi4nAU1ML/630ToTV9ee0rLdcSEXE0CmrBx9OdFwe0Zu+RU3yxPc3e5YiIyEUU1ALAoA71iWhai3+u2kdefqG9yxERkfMU1AJcuLpWO7LPFPBvLdcSEXEYCmqxad8wiD93bsicjQdJzT5j73JERAQFtfzB83e2wsPdYPIyLdcSEXEECmopoW6gD2Nua86q5GN8/0uWvcsREXF5lw1qs9nMrbfeSlxcHHFxcezbt4/p06fz5z//mddee822XVlj4rz+GnkjDWv56upaIiIO4LJBvW/fPgYOHEh8fDzx8fEUFhaSlJTEwoULueGGG/j+++/ZvXt3qTFxbj6e7oyPakPK0Tzmb9NyLRERe/K43I0//vgja9euJTExkZYtW3LjjTfypz/9CcMwiIyMZP369QQEBJQa69GjR1XVL9fJgLB63Ny0Nu+u2seg8PoE+njauyQREZd02Y66ffv2zJkzh4ULF2I2mzl37hwhISEABAUFkZWVhclkKjUmzs8wDF4Z3JYTpgLe+3a/vcsREXFZlw3q1q1bU7duXQDCwsLw8/Pj3LlzAJhMJqxWKzVq1Cg1JtVDWGgQQ7s0ZO73B/ktS8u1RETs4bJBPW7cOFJSUigqKmL16tWYTCaSkpIASElJITQ0lHbt2pUak+rjuTtb4e3hruVaIiJ2ctmgHjNmDOPGjWPIkCF06tSJxx57jOTkZN544w1mz57NwIED6dKlS6kxqT7qBhQv11q99xgb9+tjDRGRqmZYy3msOj8/n7Vr19KuXTsaNWp0ybHLiY6OZtGiRRWrWKpcfmERd0xZh5+nB8uejMTDXcvvRUTKq6LZV+5/cX18fOjfv3+JQC5rTKoPH093Xo5qw75jeXym5VoiIlVKrZFclTvb1aPbjbX516p95J7V1bVERKqKglquyoXlWjlnC5mu5VoiIlVGQS1XrV2DIEZENOI/3x/k1+On7V2OiIhLUFBLuTxzRyt8PLVcS0SkqiiopVyCA7x5om9zvk3JZP3Px+1djohItaeglnJ7sGdTmtzgx6SlyZiLLPYuR0SkWlNQS7l5exRfXWt/5mnmbT1k73JERKo1BbVUyJ/ahtCj2Q3865ufyTEV2LscEZFqS0EtFWIYBn8b1JZTZwuZpuVaIiLXjYJaKqxN/UBG3NyY+M2p/JKp5VoiIteDglquyTN3tMTX053Jy5LtXYqISLWkoJZrUsffmydvb8F3+46zdl+mvcsREal2FNRyzR7o0ZSm55drFWq5lohIpVJQyzXz8nDj5YFtOXD8DAlbUu1djohItaKglkrRr01dIpvXYcrq/Zw8o+VaIiKVRUEtlcIwDCYMakNevpZriYhUJgW1VJrW9QKJ6daY+C2p7D+WZ+9yRESqBQW1VKpn7miFn5c7k5btxWq12rscERGnd1VBnZWVxZAhQwAYP348w4cPZ+bMmbbbyxoT11S7hhdP3d6C9T8fZ+0+XV1LRORaXVVQv/POO+Tn57Nq1SosFgvz588nLS2NgwcPljkmru3+7k25qU4NJi3Tci0RkWt1xaDevHkzvr6+BAcHs3XrVgYMGABAZGQkSUlJZY6JaytertWGX4+fIX6zlmuJiFyLywZ1QUEBM2fO5LnnngPAZDIREhICQFBQENnZ2WWOifRtXZdeLeowdfXPnNByLRGRCrtsUM+ePZuYmBgCAwMB8PPzIz8/HygObYvFUuaYyIWra50pKGLKNz/buxwREad12aDevHkz8+bNIy4ujr179/Ldd9/ZDm2npKQQGhpKWFhYqTERgJYhAcR2a0xCYir7jmq5lohIRXhc7saEhATbn+Pi4nj//feJiYkhMzOT9evXs2DBAgzDKDUmcsHYfi1ZvCODN5Yl88lfbsYwDHuXJCLiVK56HXV8fDz+/v7Ex8cTHh7OJ598QkBAQJljIhfUquHF0/1asmF/FmtSdHUtEZHyKvcJT4KCgoiKiiI4OPiyYyIXxHVvQrPgGryxbC8FZn2HQUSkPHRmMrnuPN3dmDCoLb9lneGTzQftXY6IiFNRUEuVuK1VXfq0DGbat/vJPn3O3uWIiDgNBbVUmb8NaoOpoIh/abmWiMhVU1BLlWleN4C4W5rw2dZD7D1yyt7liIg4BQW1VKmn+7Ug0NeTN5Yl6+paIiJXQUEtVaqmnxdj+7Vk0y/ZfJN8zN7liIg4PAW1VLmYbo1pXtefycv3cs5cZO9yREQcmoJaqpynuxt/G9SW1GwT//n+oL3LERFxaApqsYs+LYPp27ou7337C1lariUickkKarGblwe24WxhEe+u0nItEZFLUVCL3TQL9uf+7k2Zv+0QyYe1XEtEpCwKarGrp25vQZCvJ68v3aPlWiIiZVBQi10F+XnyzB0t2fLrCb7eo+VaIiJ/pKAWuxt5c2NahvjzppZriYiUoqAWu/M4v1zr0AkTczYdtHc5IiIORUEtDqFXi2D6tanLv9f8wvE8LdcSEblAQS0OY3xUG86Zi3h31T57lyIi4jAU1OIwbgr254HuTZm/PY3dGbn2LkdExCFcVVDn5OSwadMmTpw4cb3rERf3xO0tqOXnxetLdXUtERG4iqDOzc1l9OjR7Ny5kwceeIATJ04wfvx4hg8fzsyZM23blTUmUl5BvsXLtbb+doKVu4/auxwREbu7YlDv27ePF198kUcffZTIyEi2bNmCxWJh/vz5pKWlcfDgQVatWlVqTKSiRkQ0onW9ACYv30t+oZZriYhru2JQ33zzzXTs2JFt27axc+dONmzYwIABAwCIjIwkKSmJrVu3lhoTqagLy7XST57lo42/2bscERG7uqrPqK1WK8uXLycwMBDDMAgJCQEgKCiI7OxsTCZTqTGRa9GzeR3uaBvCzO9+IfNUvr3LERGxm6sKasMwePXVV2nVqhU7duwgP7/4H06TyYTFYsHPz6/UmMi1ejmqDQVFFv7xtZZriYjrumJQz549m8WLFwOQl5fHww8/bDu0nZKSQmhoKGFhYaXGRK5V0zo1eKjnjSz8IZ1d6VquJSKuyeNKGwwfPpynn36aL774ghYtWtCvXz9iY2PJzMxk/fr1LFiwAMMwiImJKTEmUhke79uc/yal8/rSPSx4pDuGYdi7JBGRKmVYK7BYNTc3l02bNhEREUFwcPAlxy4lOjqaRYsWVaxicTnzEg8x/n+7+HdMJwZ1aGDvckREKqSi2VehM5MFBQURFRVVIpDLGhOpDMMjGtGmfiBvLU/Rci0RcTk6hag4PHc3g78NakNGzlk+3PCrvcsREalSCmpxCj2a1eHOdiHMXHuAY1quJSIuREEtTmN8VBvMRVb+vlLLtUTEdSioxWk0uaEGD0U25b8/pPNTWo69yxERqRIKanEqj9/WnDr+3rq6loi4DAW1OJUAH0/G3dmSpNSTfLXziL3LERG57hTU4nTu7dKItvUDeVtX1xIRF6CgFqfj7mbwyuC2HM7NZ/Z6LdcSkepNQS1O6ZabbmBAWD3eX3uAo7lariUi1ZeCWpzW+Kg2FFmt/H1lir1LERG5bhTU4rQa1fZjVOSNLNqRwY5DJ+1djojIdaGgFqf22G3NCQ7Qci0Rqb4U1OLU/L09GHdnK3YcymHJT4ftXY6ISKVTUIvTu7dzQ8JCA3l7RQqmArO9yxERqVQKanF6bm4GrwxqxxEt1xKRakhBLdXCzTfWZmCH+sxad4DDOWftXY6ISKVRUEu18WL/1lis8I6Wa4lINaKglmqjUW0/Hu51E1/+eJikVC3XEpHq4YpBnZeXx6hRo/jLX/7CmDFjKCgoYPz48QwfPpyZM2fatitrTKSqPXprM+qeX65lsWi5log4vysG9ZIlS3jooYf4+OOPqVOnDsuXL8disTB//nzS0tI4ePAgq1atKjUmYg81vD14vn9rfkrL4cufMuxdjojINbtiUMfGxtKzZ08ATp48yZIlSxgwYAAAkZGRJCUlsXXr1lJjIvYS3SmUDg2DeGfFPi3XEhGnd9WfUe/YsYPc3Fzq1atHSEgIAEFBQWRnZ2MymUqNidhL8XKtthw9lc+stQfsXY6IyDW5qqDOyclh0qRJvPnmm/j5+ZGfX3y1IpPJhMViKXNMxJ66Nq3N4PAGfLD+VzK0XEtEnNgVg7qgoICnnnqKZ599ltDQUMLCwmyHtlNSUi45JmJvLw5oDcCo/2wn5egpO1cjIlIxHlfaYOHChSQnJzNr1ixmzZpFdHQ0X375JZmZmaxfv54FCxZgGAYxMTElxkTsLbSmLzNjO/P8wp0Mfm8jT/Ztwehbm+HprlWJIuI8DGsFLjmUm5vLpk2biIiIIDg4+JJjlxIdHc2iRYsqVrFIOWWfPscrS/awbOcRwkIDeXdoR1rVC7B3WSLiYiqafRVqLYKCgoiKiioRyGWNiTiCG/y9mRHTmZmxnTmSk8+g9zbw7zX7MRfpuxQi4vh0DFBcRlT7+qwa25s/tavHP1f9zD0zv2ff0Tx7lyUiclkKanEpF3fXGTlnGfzeRmZ894u6axFxWApqcUlR7evzzdje3NE2hH98vY/o97/n52PqrkXE8SioxWXd4O/NjNjOzIjpTPrJswyaru5aRByPglpc3sAOxZ9d92tbV921iDgcBbUIUMffm5mxXfh3TCd11yLiUBTUIhcZ1KEBq8b25vY2xd31n9//nv3qrkXEjhTUIn9Qx9+b9+8r7q4PnTAxcPpGZq5Vdy0i9qGgFrmEQR0a8M0zfbi9TV3+vlLdtYjYh4Ja5DKKP7vuzHsjf++u3197QN21iFQZBbXIFRiGweDwBqwa24e+revyzsoU/jxrs7prEakSCmqRqxQc4M3793Vm+shOHMo+w8D31F2LyPWnoBYpB8MwuOt8d31bq2Bbd/1LprprEbk+FNQiFRAc4M2s+7owfWQnUrPPEDV9I7PWHaDIUu6rxoqIXJaCWqSCLnTX35zvrt9ekcKf3/+eXzJP27s0EalGFNQi1+hCdz1tREcOZp8havoGPlB3LSKVREEtUgkMw+DujqGsGtubW1sG89aKFO6dpe5aRK6dglqkEtUN8OGDuOLu+rcsddcicu0U1CKV7OLuuo+6axG5RlcV1FlZWcTExABQWFjI6NGjGTFiBAsXLrzkmIirqxvgw+zz3fWvx4u769nr1V2LSPlcMahzc3N54YUXOHv2LACffvop7dq14/PPP+frr7/m9OnTZY6JyO/d9TfPFHfXby5PYeis7zlwXH9HROTqXDGo3d3dmTp1Kv7+/gAkJiYyYMAAACIiIti9e3eZYyLyuwvd9dThHTlw/AxR0zbwf+t/VXctIld0xaD29/cnICDA9vvZs2cJCQkBICgoiOzs7DLHRKQkwzAY0imUb8b2pleLYCYv38uwDzaruxaRyyr3l8n8/PzIz88HwGQyYbFYyhwTkbLVDfTh/+7vwpTh4fySeVrdtYhcVrmDul27diQlJQGQkpJCaGhomWMicmmGYXBPp4bnu+s6tu76V3XXIvIHHuXd4Z577uHhhx9m+/bt/PLLL4SHhxMSElJqTESurLi77sriHzOYuCSZAdM2MO7OVjzU80bc3Qx7lyciDsCwWq3lPt527NgxkpKS6NWrl+3z67LGLiU6OppFixZVrGKRairzVD7j/7eL1Xsz6dqkFn+/twM3BfvbuywRqSQVzb4KnfAkJCSEqKioEoFc1piIXL0L3fW/hoXz87E8BkzbwIcb9Nm1iKvTmclEHIhhGER3bsjqZ/rQq0Ud3li2l+EfbOa3rDP2Lk1E7ERBLeKASnfX6/lo429Y1F2LuBwFtYiDutBdf/NMH3o2q8OkpckMn72Zg+quRVyKglrEwYUE+vDhA115d2g4+47m0X/aej5Wdy3iMhTUIk7AMAz+3KW4u+7RrA6vq7sWcRkKahEnEhLow0cPdOWfQ8NJUXct4hIU1CJOxjAM7u3SkG/G/t5dj5i9Rd21SDWloBZxUvWCfu+u9x49Rf9p65mzSd21SHWjoBZxYhd3191vuoHXvkpmxP9tITVb3bVIdaGgFqkG6gX58PGDEfzj3g7sPXKK/lM3MFfdtUi1oKAWqSYMw2Bo10Z8M7YP3W6qzUR11yLVgoJapJqpF+TDnAcj+Lu6a5FqQUEtUg0ZhsGwro1YNba3rbse+X9bOJRtsndpIlJO5b4etYg4j/pBvsx5MIIvktKZ9FUy/f61jgY1fQjy86KmrydBvp7U9POkpq8ngb6e1Dw/XtOv+Lag8z+9Pdzt/VREXJaCWqSau9Bd92pRh482/EZm3jlyzhaSc7aQ1Owz5Jwt5NTZQi53ZNzX090W3rafvl7U9LsQ8L//HnTRGwB/bw8Mw6i6JytSDSmoRVxE/SBfJgxqW+ZtFouVvHNmck2F5JwtIPdsITmm4jDPNf3h97OFHMwykXM2hxxTIefMlks+prubcT7ULw704s490NezRPde/PP3sPd01ydzIqCgFhHA7XygBvl60hi/cu2bX1j0e5BfCPWzheSaCs//uYCc838+caaAX4+fIcdUQN45M9bLdPE1vNxLBbqtg78o0GtedIi+pp8XNbzc1cVLtaKgFpFr4uPpjo+nOyGBPuXar8hiJS+/ZKeec1H3/vvP4rFfMk/b3gAUFF26i/dwMy4K9OLwDvrD5/FB5w/VB/h44O3hjo+nG94e7nh7uuFz/qeXuxtubgp8sb9KDerx48dz4MAB+vTpw2OPPVaZdy0i1Yy7m1H85TU/r3LtZ7VayS+0lOjULwS67ffzgZ5ztoDMvHx+PpZHrqmQvHPmcj2Wl4cb3h5uF4W52yWD3dvDDR9P9ytu413WNhf2Pb+Nl7ubjgqITaUF9apVq7BYLMyfP5+XXnqJgwcP0rRp08q6exERoPjLcb5e7vh6+VI/yLdc+5qLLJzKN5NjKiDnbCGn882cM1s4Zy4iv7D457lCC/nnf54zW8gvLLJtc66w5LZnzph/v/2i/fLNRZc9rH/l58ilg//i8RJvAi76WWLfi7d3x+eiNwt/3MYwwGoFK8VviIp/AlawYi3zNmvxjVgBi/UP25yfA+tl9qfE+EXbXerPV9r/ErVd+N1y0TaUeozfb6vj702XJrUq/j+xElVaUG/dupUBAwYAEBkZSVJSkoJaRByKh7sbtWt4UbtG+br48rJarRQWWYvD/eKwL/Em4KI3B2YL52xvCC7evuQ2F8bPFhZx0lRQ8k3GRfvLtXN3M0ia0K/cR3yuh0oLapPJREhICABBQUFkZGRU1l2LiDgVwzDw8jDw8nAjoIof22q12gL73EVHA650xCC/sAgrYFDc0RsYXDj6bhjGRePnfz//Z87f5nbR2IX9uXj7C+OXvW+jzMcoa/8y75uL6yp9f27n9+ePj1PGc65do/wfy1wvlRbUfn5+5OfnA8WhbbHoXZ2ISFUzDMP2BT98Pe1djlSCSluoGBYWRlJSEgApKSmEhoZW1l2LiIi4rErrqPv160dMTAyZmYEEPlEAABBXSURBVJmsX7+eBQsWVNZdi4iIuKxK66j9/f2Jj48nPDycTz75hICAqv5kRkREpPqp1HXUQUFBREVFVeZdioiIuDSdTFdERMSBKahFREQcmIJaRETEgSmoRUREHJhdrp6VkZFBdHS0PR5aRETELip6xk7Dar2WU8eLiIjI9aRD3yIiIg5MQS0iIuLAFNQiIiIOTEEtIiLiwKosqNPT0yu0X2FhIfb8vltaWtp1uV/Nx+80FyVpPkpy5vmo7Md35rm4HlxlPqpkedbs2bNp2rQpDRs2LDE+ZcoUOnfuTJ8+fUqMW61Wnn32Wd58800++ugjfvjhB6xWK4ZhsG3bNiZMmMCwYcMA+Oqrrzhx4gQjR45k3759ZGVlkZaWxs8//4yHhwcTJ068ptrnzZtH3759iYiIuKb7uZgzz8eaNWuoXbs2gwcPvqb7ucCZ5iInJ4c9e/bQpk0bateuXelzAc41H3+k+SgpJSWFFStW8PDDD1/T/VzgzHOh10ZJ5Z2P6748Kz09nY8//phXXnkFKJ7E3bt3A3Do0CECAgKoVasWBQUF3H///dxxxx0AzJ07F29vb0aOHMnp06cZPXo0N910E61atSI2NtZ2/0VFRYwfP55XXnmFDz/8kIYNG/L555/z/vvvU6dOnXLXm5WVxZNPPsm8efMAMJvNPPPMM0ydOhU3t2s/AOFM85GXl8fYsWOxWCz4+voyZcoUvLy8ePbZZ3nttdfw9/d3mbnIzc3lkUceoU+fPixfvpz//Oc/1K5du9Lmwtnm44KsrCxGjRrF4sWLAVx2PsxmM/369aNRo0YATJgwgVatWjFp0iQeeuihUmFSnefigokTJ9K7d2/69u0LuO5rY968eaxYsQKAU6dOER4ezuuvv16u+bjuQT1jxgz69+9Ps2bNSt323nvvER4eTu/evUvdZrFYMAyDd999F5PJxK+//srRo0fp0aMHOTk5/Otf/yqxfXp6Ovn5+RiGwcsvv8zkyZOxWq3UqFGD+vXrX1Wtubm5PPPMM5w4cYL//e9/tvF58+bRsmVLunbtWs5nX5ozzUdCQgJNmzalZ8+evPrqq/Tu3Zvbb7+ddevWcfLkSYYMGVKxSTjPmeZi69ateHl50bFjR9555x169OhBr169Km0uwLnm44Jx48axa9cuVq5cCeCy87Fnzx6WL1/OuHHjSowfOHCAlStXMmbMmHI889KcaS4Atm/fzty5c/n3v/9tG3PV18bFJk2axJAhQ2jfvn255uO6f0adlpZmm0yz2YzZbL7ktgUFBVgsFhYvXsz9999Pamoqv/32G7169aJ79+507tyZXr16cfLkSQC++OIL7rrrLqZMmUJSUhLLli1j2bJlHD9+nGXLlrF06VJ27tx51bW6u7szderUUu9wOnbsSHJycgWefWnONB+xsbH07NkTgJMnT3LDDTcAxfOxd+/eik6BjTPNxc0330zHjh3Ztm0bO3fupFOnTkDlzQU413wAbN68GV9fX4KDg21jrjofP/74I2vXruXee+9l/PjxtlqbNWtW4c9RL+ZMc1FYWMiECRMIDQ1l9erVtnFXfW1ccOzYMbKysmjfvj1Qvvmo0lOIrl+/noSEBKD4EMDOnTvx8fGxdapms5nXX3+dIUOGkJaWxrlz5/Dw8MBkMpGfn09BQQEmk8l2f0OHDqVx48Zs3bqVu+++2zb+ww8/8OSTT5a7vksdgvD29iY/P7/c93cljj4fF+zYsYPc3Fw6duwIgI+PT6XPhzPMhdVqZfny5QQGBuLhUfxX53rMBTj+fBQUFDBz5kxmzJhRolt01flo3749c+bMoW7dujz//POsW7eO22+/HaDSv7Tk6HOxePFimjdvzqhRo/j00085cuQIcXFxLvvauCAhIYGRI0fafi/PfFz3oPb29ubMmTPUqFGDvn370rdvX8xmM48//jh9+/bl9OnT9O/fn6FDh5ba1zAMrFYrX3zxBadOneLMmTNkZWVx4sSJMh9rxIgReHp6YjKZiIuLw8vLi48++uian0N6enqFDnOUxdnmIycnh0mTJvHee+/ZxiprPpxtLgzD4NVXX2Xq1KmsWbOGqKgol31tzJ49m5iYGAIDA0uMu+p8tG7dGi8vLwDCwsJITU0F4MyZM3h7e1fg2ZfkTHOxd+9ehg0bRnBwsK0zjYuLc9nXBhQfck9MTGTs2LG2sfLMx3UP6j59+rBq1SruueceoPiFO27cOO68807S09Np37498+fP59SpUzz44IO4u7uX2L+oqIi5c+eSmJjI7t27ueeee3juuedKbJOamsq4cePw8fFh7ty5tvG4uLhKeQ7ffvstTz/9dKXclzPNR0FBAU899RTPPvssoaGhtvEVK1bQr1+/cj7z0pxpLmbPnk3dunUZMmQIeXl5BAQEAJU3F+Bc87F582YSExOZN28ee/futX1+56rzMW7cOB599FFatGjB6tWrGT16NABff/11qW8fV4QzzUXjxo1ty4927dpFgwYNANf9uwLFn9l36NABwzBsY+WZj+v+GfVtt93G2rVryc7OZseOHYwcOZLBgwfbJtjNzY1p06Zx7NgxoqKi2L59O0VFRSQnJzNnzhzatm0LQIMGDQgPD+fHH38kOjqagoICAI4cOcKGDRt4/PHHycnJ4cEHH7T9d8GxY8eYPHlyherfvn07tWrVonbt2tc2Eec503wsXLiQ5ORkZs2aRVxcHMuXL+e3337j2LFjtG7d2qXmYvjw4Xz55ZfExsZSVFREZGRkpc6Fs81HQkIC8fHxxMfH06ZNGyZPnuzS8zFmzBjGjRvHkCFD6NSpEz169ODEiROsW7eO2267zaXm4t577yUxMZHY2Fg+++wz/vrXv7r0awNg48aNJZb4lns+rFXgyJEj1iVLlljz8/Ot6enptvG3337bumbNmhLbmc1m65w5c6zTpk2zbty40frSSy9ZH3roIWtsbKx1+PDh1kGDBlkjIyOtH3/8sdVqtVpzcnKs27Zts1qtVmtMTEyJxx0xYoTVarVazWazde7cuRWq/eOPP7YWFBRUaN9Lceb5mDdvnjUvL69C+5ZFc1GS5qMkZ56PJUuWWA8fPlyhfcvizHOh10ZJ5Z0Ph7zMpdlsxs3NrVLWLUPxYQ6z2VwpnxXZg+bjd5qLkjQfJWk+fqe5KMmZ58Mhg1pERESK6aIcIiIiDkxBLSIi4sAU1CIO7ujRo1gsFnuXAcDx48dJSkqydxkiLkVBLWIHX331FbNnzwaKzzH/1FNPkZOTU2Ygjxo1ynZ6w6vRo0cP4uLi6NmzJwsXLmTgwIE8+OCD3HfffTz77LO27d58802ysrIASEpKYurUqWzfvp133nnHts2ZM2dKnOxm8eLF7Nmzp8zHnTZtGlu2bGHKlCnMnj2b06dP89e//pWioqKrrl1ESqvSU4iKuLodO3ZQq1YtPD09bSdh8PDwIDMzkx07drBy5UqGDx/OG2+8QY0aNbBYLGRkZDBmzBjc3d1xc3MjLy+PDz74gJCQkDIfIyIigmnTpvHiiy/i5+dHy5YtCQsL4+zZsyXOO92jRw+2bdvGxo0bSU5O5vTp02zZssVWy5QpU6hfvz5Hjhzhiy++YOjQoSxduhRvb2+++eYbAAIDA5kxYwZnzpzB39+fH3/8kezsbI4fP87hw4fx9fXF3d3d9gaksr5xK+JKFNQiVSg4OJgXX3yRYcOGYbVaKSwsxMPDAw8PD2677TaCg4Np06YNixYtAmDq1Kncc889NGnShI0bN5Y4BeGlbNu2jbi4OH799VciIyPp3r07d999d4mrA5nNZnr27ImHhwd33HEHW7ZsITk5me7du/P111+XOEvThAkTeOWVV6hfvz5du3bliSeewNfXl9TUVNtRgdOnT5OTk8Onn35KmzZt6NSpE59++impqanExsaSmprKzJkz6dChw3WYVZHqTW9vRapQw4YNeeutt/Dy8uLUqVPcf//9PPDAA+zcuZO4uDjefvtttm3bBsDy5cvZuHEjFouFiIgIjh49ytKlS6/4GAsWLCA0NJRVq1YRGBjI8uXLefzxx5kxYwb16tUD4Msvv6R///7MmzcPq9VKfn5+ifN2m81mWxfs5+fHP//5TzZv3syYMWNYsGABy5YtIz093Xbhezc3NzIyMvjLX/5CvXr1yMrKYufOnYwdO5bXX3+dAQMGKKRFKsh94sSJE+1dhIgrCQoK4oMPPqBhw4ZMnjyZdu3aMX/+fJYuXcqwYcPw8vLinXfeYfv27cyYMYPPP/+cWrVqMXLkSD755BMSEhJo06bNJS9g/8ILL+Dj48P8+fMJCgoiPDyc7OxsJk6cyNq1a7nlllto3749NWvWxN3dnXfffZeVK1eyd+9eNm3axM6dO/n2229p2LAhDRs2BODll1/m+eefJyAgwPYGYPDgwTRt2hSAvLw822X7oqOj6dKlCzt37sTNzc12CD88PLyqplikWtGhb5EqZLFYmDx5MufOnbONbdq0iZtvvpmNGzfSr18/fHx86NChA6+99hqGYfDcc8+RkZGBl5cXkydPZvfu3TRv3vySj+Hj4wPAzJkzSUpKIisri7CwMB555BEGDx5su8rTBXPmzGHChAncd999FBYWljr0vXnzZqD4ikVWq5UdO3Zw5MgRRowYYdsmIyODqVOnkpqaSkpKCnv37uXbb7/liSeeAKB3797XPnkiLkqHvkWqUGJiIsHBwURFRQHFVyhbsWIFkyZNIj4+nqKiIgIDA9m+fTvZ2dm2Dnvq1KkArFmzhs8++6zU1YDKkpycTGZmJgC33norZrOZu+66q9R2hw8fZv/+/bRu3Rqr1Vri+skmk4kpU6bYvi0+e/ZsBg0aROfOnZk+fbptuy5dujBgwAC6d+/OXXfdxeDBg/H09KRt27asXr2a9u3bV3zSRFycglqkCnXv3p3Ro0dTVFSExWLh3XffZfDgwYSGhtKjRw8mTJhAVlYWhw4dok6dOphMJmrWrMmHH37IgQMHOHbsGCdPnmTr1q1lXr1n/fr17Nixg7Nnz7Jnzx68vLxISEhg48aNjBgxgvvvv5/k5GROnTrFkiVLmDt3LtOnT+epp54Cij+PbtKkie3+1q1bx8CBAzGbzbzwwgtkZmZy//338+CDD5KTk8OYMWNslzQcOXIk3bt35/3332f//v2kpaXxyy+/4OXldcklXSJyZTrXt4gdLF68mK+//hp3d3emT5+Om5sbVquVqVOn0rt3b7Kzs/nTn/7EY489xsmTJ21LuB5//HH69euHr68vRUVFfPrppzzwwAO2+83MzMTHx8f2xbBt27Zx4sQJ7rzzTtvvAQEBbNu2jTp16tC8eXMWLlzIvn37OHv2LPn5+bi7u5OTk8PTTz9t68C/++47TCYTAwcOLPE8li5dSvPmzalfvz4TJ06kUaNGPPLIIxw4cIB//OMfPP/889SpU4cnn3ySd999l8aNG1fRDItUHwpqESd1Pa7eU1hYaOv2vb29r+oQ+wVmsxkPj+KvvVitViwWi21/q9WKYRiVVqeIK1FQi4iIODB9Ri0iIuLAFNQiIiIOTEEtIiLiwBTUIiIiDkxBLSIi4sD+P4mGaUaQKLQYAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 576x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#将所有未通话的广告线索人数进行筛选出来并按跟进次数求和\n",
    "data_NoAnswer = data_user_source.loc[data_user_source['状态']=='未通话',:]\n",
    "data_NoAnswer.insert(6,'辅助列',1)\n",
    "data_NoAnswer.groupby(['状态','跟进次数'])['辅助列'].sum().plot(figsize = (8,4))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "  * 对于没有接通电话的人数，前面四次拨打绝大部分都能接通，对于未接通的电话很多只跟进了一两次，需要改进"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9951"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAl4AAAEGCAYAAAC0FJuBAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAAgAElEQVR4nO3deUDUdf7H8dcMN3J4gEh4gZaaqJVHqaD5yzaPtcytLM2jvNq1Y808OrbL1Wq3NrfD8ujyKl3X1FxLzcyDPJAsTaXyAAFPTlFAGOb7+wMdoVAOgS8Mz8c/znzme7zn7Ygvvp/vfL8WwzAMAQAAoNJZzS4AAACgtiB4AQAAVBGCFwAAQBUheAEAAFQRghcAAEAVIXgBAABUEVezC7jo5ptvVkhIiNllAAAAlCgpKUk7duwo83rVJniFhIRo+fLlZpcBAABQokGDBpVrPaYaAQAAqgjBCwAAoIoQvAAAAKoIwQsAAKCKELwAAACqCMELAACgihC8AAAAqki1uY5XZcvKtWns/BilnsuVm4tFLlaLXK1WuToeW+TqYpWrteC5m4u10HjBsi6Oxxa5WK1ys1rkcuF54W25FVm2+O38tobi9/v7da0WyWKxmN1OAABQDrUmeFktFjVt4C1PN6tsdkP5dkN5+Xbl5duVnWfIlm9cGLcXelywTL694Lkt3+4Yt9kN097L5ULb5cPfZYJjMaEz8tpA9W8fbNp7AwDAmdWa4OXp5qIZd7ersO0ZxqUAZrMbys83ZLPbizzPs18IbYVe+32YuxD27EXDX16+UXzgyy+8ncus66inYL+Fa8ixFb9uvt3Qmew8ffHjMUVcGyB/L7cK6xUAAChQa4JXRbNYLhw9cjG7korzU1KG/vj2Vn2286jG9WxhdjkAADgdTq6HQ3iIv7q1aKCPouKUa7ObXQ4AAE6H4IUixkSG6cSZHP1v7zGzSwEAwOkQvFBEz+sC1bKhj+ZuPiLDMO8LBAAAOCOCF4qwWi0aExmq/cfP6LtDKWaXAwCAUyF44XfuuiFEAT7umrvlsNmlAADgVAhe+B1PNxcN79pc3/58Wr+ezDS7HAAAnAbBC8V68JZm8nSzat6WI2aXAgCA0yB4oVj167jrno6N9fnuJJ3KzDG7HAAAnALBC5c1KiJMeXa7FmyLN7sUAACcAsELlxUaUEe92wRpwfZ4Zefmm10OAAA1HsELVzS2R5jSs/K0LCbB7FIAAKjxCF64ok7N6qlDk7r6YOsR5du5oCoAAFeD4IUrslgKLqgal5Klrw+cNLscAABqNIIXStSnbSM1rueleVxQFQCAq0LwQolcXax6uHuoouPStPtomtnlAABQYxG8UCr3dW4iX09XLqgKAMBVIHihVHw8XDX05mb68qfjSkjNMrscAABqpFIFr+TkZA0cOFCS9Mwzz2jw4MGaNWuW4/XSjqFmG9mtuawWiz7YylEvAADKo1TB67XXXlNOTo7WrVsnu92uJUuWKCEhQXFxcaUeQ83XyN9Td3a4Rkt3JSgjK8/scgAAqHFKDF7btm2Tl5eXAgMDtXPnTvXt21eSFBERoZiYmFKPwTmMjgxTVm6+Fu88anYpAADUOFcMXrm5uZo1a5aeeuopSVJWVpaCgoIkSf7+/kpJSSn1GJzD9df4KaJlgD7+7ohybXazywEAoEa5YvCaM2eOhgwZIj8/P0mSt7e3cnJyJBWEMLvdXuoxOI/RkaE6eea8vvjxmNmlAABQo1wxeG3btk2LFy/WsGHDdODAAW3cuNExbRgbG6uQkBCFh4eXagzOo+d1gbouyEdztxyWYXAbIQAASsv1Si8uWrTI8XjYsGF67733NGTIEJ06dUqbN2/W0qVLZbFYSjUG52GxWDQ6MkyTl+1R1MEURVwbYHZJAADUCBajjIcsMjIyFBUVpc6dOyswMLBMY1cyaNAgLV++vBxvAWY4b8tXxGsb1SbYT/Mf7mJ2OQAAVKny5pYrHvEqjr+/v/r161euMTgPD1cXjejaTK+v+0U/n8hUq0a+ZpcEAEC1x5XrUW5Db24mLzcXbp4NAEApEbxQbvXquOveTo214ocknTqTY3Y5AABUewQvXJWHu4fKZjf0ybY4s0sBAKDaI3jhqjQPqKM/XB+khduPKivXZnY5AABUawQvXLWxPcKUkZ2nZTGJZpcCAEC1RvDCVevYrL5ubFpX87YcUb6dC6oCAHA5BC9UiDGRYTqamqX1+0+YXQoAANUWwQsV4o62jdSkvpfmbjlidikAAFRbBC9UCBerRaO6hyomPk0x8WlmlwMAQLVE8EKFubdTE/l5unJBVQAALoPghQpTx8NVQ29pprX7TuhoSpbZ5QAAUO0QvFChRnZrLherRR9Gca4XAAC/RfBChQry89SdHUK0JDpB6Vm5ZpcDAEC1QvBChRsdGarsvHwt2nHU7FIAAKhWCF6ocG2C/RR5bYA++S5O5235ZpcDAEC1QfBCpRgTGaZTmee16odjZpcCAEC1QfBCpYi8NkCtG/nqg61HZBjcRggAAInghUpisVg0OjJMsScyteXXZLPLAQCgWiB4odLc2eEaNfT10FwuqAoAgCSCFyqRu6tVI7o115Zfk3Xg+BmzywEAwHQEL1SqoTc3lZebi+Zx82wAAAheqFx1vd01uHMTrfoxSSfP5JhdDgAApiJ4odI93D1U+XZDH38XZ3YpAACYiuCFSte0gbfuaNtIi7bH69x5m9nlAABgGoIXqsSYHmE6k2PTf3YlmF0KAACmIXihStzUtJ46NqunD6KOKN/OBVUBALUTwQtVZkxkqBJSs7V23wmzSwEAwBQEL1SZ269vpGYNvLmgKgCg1iJ4ocq4WC0aFRGq3UfTFROfanY5AABUOYIXqtQ9HRvL38tNczZz1AsAUPsQvFClvN1d9eAtTbVu/0nFJZ8zuxwAAKoUwQtVbkTX5nKzWvVhFLcRAgDULgQvVLmGfp6664ZrtHRXgtLO5ZpdDgAAVYbgBVOMjgxTTp5di3bEm10KAABVplTBKz09XVFRUUpN5ZtoqBitGvmq53WB+mRbvM7b8s0uBwCAKlFi8MrIyNAjjzyiPXv2aMSIEUpNTdUzzzyjwYMHa9asWY7lSjsGXDQmMkynM89r5Q/HzC4FAIAqUWLw+vnnnzV16lT9+c9/VkREhLZv3y673a4lS5YoISFBcXFxWrduXanGgMK6t2yg1o18NW/LYRkGtxECADi/EoNXly5ddMMNNyg6Olp79uzRli1b1LdvX0lSRESEYmJitHPnzlKNAYVZLBaN7RGmX06e1aZfTptdDgAAla5U53gZhqE1a9bIz89PFotFQUFBkiR/f3+lpKQoKyurVGPAb/2x/TUK8vPQvC1cWgIA4PxKFbwsFoteeOEFtWrVSrt371ZOTo4kKSsrS3a7Xd7e3qUaA37L3dWqkd1CtfVgsvYdyzC7HAAAKlWJwWvOnDlasWKFJCkzM1Njx451TBvGxsYqJCRE4eHhpRoDijOkS1N5u7voA456AQCcnGtJCwwePFh//etf9Z///EfXXnutevfuraFDh+rUqVPavHmzli5dKovFoiFDhpQ4BhTH39tNgzs30YJt8Zrcp7Ua+XuaXRIAAJXCYpTj62QZGRmKiopS586dFRgYWKaxyxk0aJCWL19ejrcAZ5CQmqWe/9yosT1aaGrf1maXAwDAFZU3t5TryvX+/v7q169fkTBV2jGgOE3qe6tveLAW7YjX2fM2s8sBAKBScMsgVBujI0OVmWPT0ugEs0sBAKBSELxQbdzYtJ46N6+nD6OOyJbPt2ABAM6H4IVqZXRkmBLTsvXVvhNmlwIAQIUjeKFa6d0mSM0beGvuliPcRggA4HQIXqhWXKwWjYoM048J6doVn2Z2OQAAVCiCF6qde25qrHrebpqz+bDZpQAAUKEIXqh2vNxd9OAtzfT1gZM6knzO7HIAAKgwBC9US8O7Npeb1aoPtnLUCwDgPAheqJYCfT10940hWhaTqNRzuWaXAwBAhSB4odoaHRmqnDy7Fm6PN7sUAAAqBMEL1da1Qb7q1SpQ87fFKScv3+xyAAC4agQvVGtjIsOUfDZXK39IMrsUAACuGsEL1VrXFg10fbCf5m45IrudC6oCAGo2gheqNYvFojE9QnXw1Flt+uW02eUAAHBVCF6o9v7Y/ho18vPU3C1cWgIAULMRvFDtublY9VD35vruUIp+SsowuxwAAMqN4IUa4f4uTVXH3UXzOOoFAKjBCF6oEfy93HR/l6Zavee4jmdkm10OAADlQvBCjfFQ9+YyJH0cFWd2KQAAlAvBCzVG43re6hveSIt3HFVmTp7Z5QAAUGYEL9QoY3uEKfO8TUuiE8wuBQCAMiN4oUZp37iuuoTW10dRcbLl280uBwCAMiF4ocYZExmmpPRsffnTCbNLAQCgTAheqHFua91QYQF1NHfLYRkGtxECANQcBC/UOFarRaMiQ7UnMUM7j6SaXQ4AAKVG8EKN9KebGqt+HXfN3XLE7FIAACg1ghdqJE83Fz14SzN9feCkDp0+a3Y5AACUCsELNdbwrs3k7mrVB1s56gUAqBkIXqixAnw89KebQvTfmESlnD1vdjkAAJSI4IUabVREmM7b7FqwPd7sUgAAKBHBCzVay4Y++r/WDbVgW7xy8vLNLgcAgCsieKHGGxMZppRzufp8d5LZpQAAcEUEL9R4t4TVV3iIn+ZtOSy7nQuqAgCqL4IXajyLxaIxkWE6dPqcNv58yuxyAAC4rBKDV2ZmpkaPHq2HH35Y48ePV25urp555hkNHjxYs2bNcixX2jGgMvRrF6xr/D01d8ths0sBAOCySgxeq1at0kMPPaQPP/xQAQEBWrNmjex2u5YsWaKEhATFxcVp3bp1pRoDKoubi1UPdQ/V9sOp2puYYXY5AAAUq8TgNXToUHXv3l2SlJaWplWrVqlv376SpIiICMXExGjnzp2lGgMq0+AuTeTj4cpRLwBAtVXqc7x2796tjIwMNWrUSEFBQZIkf39/paSkKCsrq1RjQGXy83TTA12a6H97jyspPdvscgAA+J1SBa/09HRNmzZNM2bMkLe3t3JyciRJWVlZstvtpR4DKtvI7qGSpI+juI0QAKD6KTF45ebm6oknntDEiRMVEhKi8PBwx7RhbGxsmcaAyhZS10v92wXr050JOpOTZ3Y5AAAU4VrSAsuWLdP+/fv1/vvv6/3339egQYO0cuVKnTp1Sps3b9bSpUtlsVg0ZMiQEseAqjAmMkyrfjymJTsTNKZHmNnlAADgYDEMo8xXnMzIyFBUVJQ6d+6swMDAMo1dzqBBg7R8+fJyvAXg9+6fs01HU7K0aXIvublwuToAQMUqb24p1/9I/v7+6tevX5EwVdoxoCqMiQzTsYwcrdl73OxSAABw4FAAnFKvVg0VFlhHc7ccVjkO6gIAUCkIXnBKVmvBbYR+Sjqj7YdTzS4HAABJBC84sbtvDFGDOu6axwVVAQDVBMELTsvTzUXDujbThthTOnjqrNnlAABA8IJzG3ZLM3m4WvXBVo56AQDMR/CCU2vg46E/dWys/36fpOSz580uBwBQyxG84PRGRYQq12bXgm3xZpcCAKjlCF5wei0CfdS7TUMt2B6vnLx8s8sBANRiBC/UCmMiw5R6Llf//T7R7FIAALUYwQu1QpfQ+mrf2F8fbDkiu50LqgIAzEHwQq1gsVg0OjJMh5PPaUPsKbPLAQDUUgQv1Br9whsppK6X5nJBVQCASQheqDVcXax6qHtz7TySqj2J6WaXAwCohQheqFUGd24iXw9Xzd1yxOxSAAC1EMELtYqvp5uG3NxUa/YeV2JaltnlAABqGYIXap2R3ZvLIumjqDizSwEA1DIEL9Q6wf5e+mP7YH2286gysvPMLgcAUIsQvFArjY4M07ncfH2286jZpQAAahGCF2ql8BB/dWvRQB9/F6e8fLvZ5QAAagmCF2qtMZFhOp6Ro//tOW52KQCAWoLghVqr53WBatnQR3O3HJZhcBshAEDlI3ih1rJaLRoTGap9x85o26EUs8sBANQCBC/UanfdEKIAH3duIwQAqBIEL9Rqnm4uGt61uTb+fFq/nsw0uxwAgJMjeKHWe/CWZvJ0s2oetxECAFQyghdqvfp13HVPx8b6fHeSTmeeN7scAIATI3gBkkZFhCnPbteCbXFmlwIAcGIEL0BSaEAd9W4TpAXb45Wdm292OQAAJ0XwAi4Y2yNMaVl5WvZ9otmlAACcFMELuKBTs3rq0KSuPtx6RHY7F1QFAFQ8ghdwgcVScEHVI8nn9PWBk2aXAwBwQgQvoJA+bRupcT0vLqgKAKgUBC+gEFcXqx7uHqrouDT9kJBudjkAACdD8AJ+477OTeTr6cpRLwBAhSN4Ab/h4+GqITc31Zd7jyshNcvscgAATqRUwSs5OVlDhgyRJOXl5emRRx7R/fffr2XLlpVpDKgpHuoWKqvFog+juI0QAKDilBi8MjIyNGXKFGVnZ0uSFi5cqLZt2+qzzz7T2rVrdfbs2VKPATVFI39P3dnhGi2NTlBGdp7Z5QAAnESJwcvFxUUzZ86Uj4+PJGnHjh3q27evJKlz58766aefSj0G1CSjI8N0Ljdfn+48anYpAAAnUWLw8vHxka+vr+N5dna2goKCJEn+/v5KSUkp9RhQk1x/jZ8iWgboo6gjyrXZzS4HAOAEynxyvbe3t3JyciRJWVlZstvtpR4DaprRkaE6eea8Vu85ZnYpAAAnUObg1bZtW8XExEiSYmNjFRISUuoxoKbpeV2grgvy0dwtR2QY3EYIAHB1XMu6wt13362xY8dq165dOnjwoDp06KCgoKBSjQE1jcVi0ejIME1etkdRB1MUcW2A2SUBAGowi1GOX+NPnjypmJgYRUZGOs7/Ku3Y5QwaNEjLly8vx1sAKtd5W74iXtuo64P99MnDXcwuBwBQDZQ3t5TrAqpBQUHq169fkTBV2jGgpvFwddGIrs206ZfT+uVkptnlAABqMK5cD5TC0JubydPNqnncRggAcBUIXkAp1Kvjrvs6NdGK3ce0Zu9x2fL5li4AoOwIXkApjevZQsF1PfWXRd+rxz826r1vDyntXK7ZZQEAahCCF1BKIXW99M3EWzVnWEc1D6ij176K1S2vbNDU/+7RgeNnzC4PAFADlPlyEkBt5mK16A9tG+kPbRvp5xOZ+vi7OH2+O1GfRSfo5tD6eqh7c/VuEyRXF36nAQD8Hv87AOXUqpGvXhnUTtufvk3P9GutxLRsPbLwe/X857d6f9MhpWcxDQkAKIrgBVylut7uGtujhTZP7qXZwzqqaX1vvfol05AAgN9jqhGoIC5Wi+5o20h3tG2k2BNn9Ml38Y5pyFvC6mtkt1D1btOQaUgAqMX4HwCoBK0b+TmmIZ/u21oJqdl6ZGEM05AAUMsRvIBKVNfbXeN6Fj8N+fTyPYo9wTQkANQmTDUCVeD305Bx+nx3kj7deWka8vbrg+RitZhdKgCgEnHEC6hiBdOQ7bVt6m2aWmgassc/Nmo205AA4NQIXoBJ6tVx1yM9W2jTpFv1/oMd1aS+l15hGhIAnBpTjYDJXF2s6hPeSH3CG+nA8TOavy1Oy78vmIbsGtZAIy9clJVpSACo+TjiBVQjbYILpiG3P10wDXk0NUvjFjANCQDOguAFVENFpyFv+s005F79fCLT7BIBAOXAVCNQjRVMQwarT3iwDhwv+Dbk8u8T9enOo0xDAkANxBEvoIZoE+ynV/9UMA05pU9rxaec07gFMer5z42as/mQMrLyzC4RAFACghdQw9Sr464/31pwUdb3H7xJIXW9NGNNrG5+5WumIQGgmmOqEaihCk9D7j9WdBqyW4sGGtGNaUgAqG444gU4geuv8dNr91yahoxLZhoSAKojghfgRApPQ7439CZdc2Ea8pZXNuiZz/fql5NMQwKAmZhqBJyQq4tVfdsFq2+7S9OQ/41J1OIdBdOQI7s1121MQwJAleOIF+DkLk5Dbnv6Nk3u00pxyec0lmlIADAFwQuoJerXcddfbm3JNCQAmIipRqCWKTwNue9Yhj75Lk7LLkxDdm/ZQCO6Mg0JAJWFI15ALdb2Gn/9454O2v70bZp0RysdPn1pGnLu5sNMQwJABSN4AVD9Ou4a36ultkzupVlDb9I1/l6avuaAbnllg579fK9+ZRoSACoEU40AHFxdrOrXLlj9Ck1D/icmUYsuTEOO7Baq/2vdkGlIACgnjngBKFZx05Bj5u/Sra9v1Lwth5WRzTQkAJQVwQvAFV2chtw8uZfeHXKTgv289Pf/HdAtM5iGBICyYqoRQKm4uVjVv32w+rcP1k9JRachI1oGaGS35urFNCQAXBHBC0CZhYf465/3dtDUvq31WXSCFmyL1+j5u9Skvpduva6hfD1d5ePpKl+Pi3+6ycfTVT4ergWvebjK19NN7q4cdAdQuxC8AJRbAx8Pje/VUmN7hGndvpP6ZFucvthzTJk5NuXbjRLXd3e1XgpnFwKZj4dboXBWNMD97jUCHIAahuAF4KoVnoaUJMMwdN5mV2aOTZk5eTp73qazOTZlXvzzwtil5zbHMknp2Tp7Pq9gLMcmWxkD3KVQVrYA5+PpKg9Xl8puFYBartKD1zPPPKNDhw6pZ8+e+stf/lLZuwNQDVgsFnm6ucjTzUWBvh7l3k7hAHf2/IXAVijAXRwr+rxogLsY7CoiwF08Mlc4wF1cjgAHoDQqNXitW7dOdrtdS5Ys0dNPP624uDg1b968MncJwIlUVoC7eNStcGA7e96mMxeCXeEjdOUKcC5Wx1G2gunTgilRX09Xebu7yMVqkeXC+7NYJIssslpU8LjQmMWigvELr8tSsJ7VsYxkvfBlhiJjFx9bLu6n6OsX92Et9PqlZS/VYrVYHH8P1t/UKcfjCzVZL9VsKabOIvv8zVjhZS/uE+VzufYV/I2UYfmK2k7xw5dd/nJrlGX7VotFzRp4y1JNP0uVGrx27typvn37SpIiIiIUExND8AJQ5So1wBWaFr0Y4IqbYj2Wnq3M83nKOp8vu2HIbhRsz5BkXHhsNyRDxoXnlx7bCy0HoGTP9W+j0ZFhZpdRrEoNXllZWQoKCpIk+fv7KykpqTJ3BwCVqqIC3NUwjKJhzH7huQo9LjxuFDdWKNxdCnVFA5+9UBiUIwBeeM1eUkAstE970XBpFFOnjEtjdtJluV2uc5dvafEvXG75sm7fqLDtl/0z0at1wzKvU1UqNXh5e3srJydHUkEIs9vtlbk7AHB6jim7y07iAKjOKvU72OHh4YqJiZEkxcbGKiQkpDJ3BwAAUK1V6hGv3r17a8iQITp16pQ2b96spUuXVubuAAAAqrVKPeLl4+OjBQsWqEOHDpo/f758fX0rc3cAAADVWqVfx8vf31/9+vWr7N0AAABUe9xnAwAAoIoQvAAAAKoIwQsAAKCKVJubZCclJWnQoEFmlwEAAFCi8l4U3mKU55KwAAAAKDOmGgEAAKoIwQsAAKCKELwAAACqCMELAACgihC8AAAAqoipwSsxMbHIc5vNJrvdXmQsLy+vyuqx2WzKzs6usO0lJCRc9Tbo0ZXRnyujPyWrDT26mi+v14b+XC16dGX0pyjTgtecOXO0f/9+GYah559/XidPntSGDRs0evRoRUZGaujQoRo1apRmz55dZL1p06bpwIEDZdrX3//+d+3bt09TpkzRmTNnLrtcWlqaHn/88XK9n+IsXrxY0dHR5V6/NvTom2++0RdffFGudZ2xP+np6YqKilJqaqok+lOSq+mPVDt6FBsbq7lz55Zr3drQHz5DJePn0JWVtT+mXEA1MTFRJ06c0NixYyVJgwYN0uzZszV16lTdcccdGj58uObMmSNPT08ZhuFIxjabTenp6fLx8ZEkHT9+3PFmfX199cADDxS7v9zcXEnS3Xffrc8//1zDhw/XDz/8oLlz5yo5OVkBAQGaMGGCGjRooDp16pT7fSUnJ+vxxx/X4sWLJUkTJ07Uk08+qY4dO8pqLVvGdcYeZWZmasKECbLb7fLy8tKbb76pESNGaOLEierVq5ej5tran4yMDD3yyCPq2bOnXn31VX3yySf0pxjJyckaPXq0VqxYUe7+SM7ZI5vNpt69e6tJkyaSpOeee0633367pk2bpsTERDVu3LhW9+eiF198UT169ND//d//8Rn6jcWLF+vLL7+UJJ05c0YdOnTQyy+/zM+hCzIyMvTUU08pJSVF4eHhevnll8v8GTLlAqrvvvuu+vTpoxYtWig3N1fu7u6SpPvuu092u11Hjx6Vp6enGjZsKFdXVz3xxBMKDQ3VhAkTdPjwYYWFhSkxMVGPP/64oqOjNXDgQM2aNUsLFy6UJE2dOlVHjx6Vl5eXpILDgP7+/vLz81Nubq7+9a9/KTAw0LHPpUuX6p133tHWrVt19OhRtWjRQllZWXr11Vd17bXXluo9ZWRk6Mknn1Rqaqo+//xzx/jixYt13XXXqVOnTrW+R4sWLVLz5s3VvXt3vfDCC+rRo4duu+02bdq0SWlpaRo4cGCt7s/OnTvl7u6uG264Qa+99pq6deumyMhI+vMbkyZN0t69e/XVV19JUrn646w92rdvn9asWaNJkyYVGT906JC++uorjR8/vlb3R5J27dqljz/+WO+8845jjM9Q8aZNm6aBAweqXbt2/By6YP78+apbt67uvPNOTZw4USNHjix7fwwTTJkyxfF448aNxogRI4y4uDjDMAxj5syZxvr1641du3YZ06dPN+x2e5F1hw4dauTm5hpTpkwxvv76a+Ott94yDMMwxo4d61hm6tSpxsGDBx3PFy5caKxfv96Ij4831q5dW2R79957r+NxUlKS8be//c2xjfj4+FK/p8zMTOPMmTPGgw8+WGR83759xieffFLq7VzkjD0q7LHHHjN2795tGIZhpKenGzNmzCjT+s7cn507dxpDhgwxMjMzDcOgP4V99913xt/+9rci/87K0x/DcM4eLVy40OjXr5/xpz/9yXj66aeNvLy8IvWUhTP2Jzc317jjjjuMGTNmGOvXr3eM8xn6vRMnThiPP/644zk/hwqsXLnSeP31142MjAzjgQceME6fPm0YRtn6Y/q9Gu0L6dMAAA0kSURBVG+99VbVrVtX58+f15tvvqnMzExt375dkydP1uHDh/XAAw/o0UcfVUREhKSCE/Dc3NyUmZkpX1/fYrdpsVg0adIkGYah66+/Xr169VJiYqL27t2rpk2bXraW06dPKyAgQFLBuTaX235xLnd40cPDQzk5OaXeTnGcpUcX7d69WxkZGbrhhhskSZ6enlfVI2fqj2EYWrNmjfz8/OTqWvDPk/4UyM3N1axZs/Tuu+8WOXJztf2RnKdH7dq100cffaSGDRtq8uTJ2rRpk2677TZJuqoT7J2lPytWrFDLli01evRoLVy4UMePH9ewYcP4DBVj0aJFRab0+DlUoGPHjtq0aZPmz5+vFi1ayN/fX1LZ+mPKyfUeHh46d+6c43loaKhiY2NltVo1ePBgHTx4UPHx8erQoYNGjBihVq1aSSpokLe3tyQpJyfHcXjxt86fP6+3335bL730kurVq6dWrVpp8+bN+v7774s9DJiXl6e0tDTt2LFDLVq0kFQwt12eD+tvJSYmKjg4uMzrOWuP0tPTNW3aNM2YMcMxVp4eOWt/LBaLXnjhBbVq1UrffPONJPpz0Zw5czRkyBD5+fkVGeff2CWtW7dWw4YNJUnh4eGKj4+XJJ07d04eHh6l3o7knP05cOCA7rvvPgUGBurOO+/Ujh07JPEZ+i273a4dO3bo5ptvdozxc6jAO++8o5deekmPPvqowsLCtHz5ckll648pwatnz55at26d4/myZcuUm5ur4OBg7dmzR1JB2v7ll1+0ZMkSxxztt99+q5tuuqnE7R87dkx169ZVZmam/P391bhxY8XGxmrcuHGOZfbv369//OMfOnbsmCZMmKCYmBitX79ePXv2lFTw2/XFIw5XY8OGDerevXuZ13PGHuXm5uqJJ57QxIkTFRIS4hj/8ssvdeutt5Z6O5Jz9mfOnDlasWKFJBX5LY/+FNi2bZsWL16sYcOG6cCBA3r22Wclla8/knP2aNKkSYqNjVV+fr6+/vprtW7dWpK0du1axzZLyxn707RpU8dX//fu3atrrrlGEp+h39q1a5fat28vi8XiGOPnUIEzZ87o559/Vn5+vn788UdHj8rSH1OCV69evfTtt98qJSVFqampWr9+vQYMGKAVK1borrvukiTdfvvtio6OdvwF5eXl6aOPPtKAAQNkGIZyc3NlGIY+++wz3XXXXTp27Jgk6ezZszp79qxiYmL06quvqnnz5nr22WfVqVMnbdy4UatXr9aiRYvk6emprl276ptvvtHbb7+tqKgoDR48WFarVYcOHXJcU+TkyZOaPn16ud7nrl27VK9ePdWvX58eqeAf3f79+/X+++9r2LBhWrNmjY4cOaKTJ086/oOozf0ZPHiwVq5cqaFDhyo/P18RERH0p5BFixZpwYIFWrBggdq0aaPp06eXuz/O2qPx48dr0qRJGjhwoG688UZ169ZNqamp2rRpk3r16lXr+3PPPfdox44dGjp0qD799FONGjWKz1Axtm7dqs6dOzue83PoknHjxun5559Xp06dlJGRof79+5e9P6U+o6yCHT9+3Fi1apXx3//+11i9erWRnZ1t/Pjjj8aXX35p9OnTx8jNzTViYmKMtLQ0w2azGWvXrjUmTJhg2Gw2Y+DAgcbs2bON6Ohoxwl3n376qWEYhjF79mxj3rx5RmZmpvH9998bAwcONFauXGkYhmE8+eSTRpcuXYxNmzYVqWXBggXG66+/bhiGYRw8eND485//bHz77beGYRiGzWYzPv7443K9xw8//NDIzc0t17qGUTt6tHjxYsdJ5PSH/lRlfwyjdvRo1apVxrFjx+jPZfAZKhk/hyq2P6ZcTuJKUlJS5Orq6jhhrbDCX0e9nIvp1c3NTYZh6PTp045zHqSC5B4aGlrqevLz82Wz2cp8fkRlokdXRn+ujP6UjB5dGf0pGT26strcn2oXvAAAAJwVN8kGAACoIgQvAACAKkLwAlDtnDhxwnHfNrOdPn1aMTExZpcBwEkQvABUiS+++EJz5syRVHBv0yeeeELp6enFBqzRo0crLS2t1Nvu1q2bhg0bpu7du2vZsmXq37+/Ro4cqQcffFATJ050LDdjxgwlJydLkmJiYjRz5kzt2rVLr732mmOZc+fO6e2333Y8X7Fihfbt21fsfv/9739r+/btevPNNzVnzhydPXtWo0aNUn5+fqlrB1C7mH7LIADObffu3apXr57c3Nzk4uIiSXJ1ddWpU6e0e/duffXVVxo8eLD+/ve/q06dOrLb7UpKStL48ePl4uIiq9WqzMxMzZ49W0FBQcXuo3Pnzvr3v/+tqVOnytvbW9ddd53Cw8OVnZ2txMREx3LdunVTdHS0tm7dqv379+vs2bPavn27o5Y333xTwcHBOn78uP7zn//o3nvv1erVq+Xh4aH169dLkvz8/PTuu+/q3Llz8vHx0Q8//KCUlBSdPn1ax44dk5eXl1xcXByB0mrl91sAlxC8AFSqwMBATZ06Vffdd58Mw1BeXp5cXV3l6uqqXr16KTAwUG3atHHcemPmzJm6++671axZM23dulUTJkwocR/R0dEaNmyYDh8+rIiICHXt2lV33XWX0tPT9a9//UuSZLPZ1L17d7m6uur222/X9u3btX//fnXt2lVr167VU0895djec889p+eff17BwcHq1KmTHnvsMXl5eSk+Pt5x1O7s2bNKT0/XwoUL1aZNG914441auHCh4uPjNXToUMXHx2vWrFlq3759JXQVQE3Fr2IAKlXjxo31yiuvyN3dXWfOnNHw4cM1YsQI7dmzR8OGDdOrr76q6OhoSdKaNWu0detW2e12de7cWSdOnNDq1atL3MfSpUsVEhKidevWyc/PT2vWrNGjjz6qd999V40aNZIkrVy5Un369NHixYtlGIZycnKK3PfRZrM5jlJ5e3vr9ddf17Zt2zR+/HgtXbpU//vf/5SYmKgBAwZIKjiSlZSUpIcffliNGjVScnKy9uzZowkTJujll19W3759CV0AfsflxRdffNHsIgA4N39/f82ePVuNGzfW9OnT1bZtWy1ZskSrV6/WfffdJ3d3d7322mvatWuX3n33XX322WeqV6+eHnjgAc2fP1+LFi1SmzZtFBAQUOz2p0yZIk9PTy1ZskT+/v7q0KGDUlJS9OKLL+rbb7/VLbfconbt2qlu3bpycXHRG2+8oa+++koHDhxQVFSU9uzZow0bNqhx48Zq3LixJOnZZ5/V5MmT5evr6wh0AwYMUPPmzSUV3E/zhhtu0IEDBzRo0CB17NhRe/bskdVqdUyZdujQoapaDKCGYKoRQKWy2+2aPn26zp8/7xiLiopSly5dtHXrVvXu3Vuenp5q3769XnrpJVksFj311FNKSkqSu7u7pk+frp9++kktW7a87D48PT0lSbNmzVJMTIySk5MVHh6ucePGacCAAb+7CvZHH32k5557Tg8++KDy8vJ+N9W4bds2SZKHh4cMw9Du3bt1/Phx3X///Y5lkpKSNHPmTMXHxys2NlYHDhzQhg0b9Nhjj0mSevTocfXNA+B0mGoEUKl27NihwMBA9evXT1LB7UC+/PJLTZs2TQsWLFB+fr78/Py0a9cupaSkOI6AzZw5U5L0zTff6NNPP3WcmH8l+/fv16lTpyRJt956q2w2m+68887fLXfs2DH9+uuvat26tQzDUOEbeGRlZenNN990fBtyzpw5+uMf/6ibbrpJb731lmO5jh07qm/fvuratavuvPNODRgwQG5ubrr++uv19ddfq127duVvGgCnRfACUKm6du2qRx55RPn5+bLb7XrjjTc0YMAAhYSEqFu3bnruueeUnJyso0ePKiAgQFlZWapbt67mzZunQ4cO6eTJk0pLS9POnTt18uRJTZ8+vcj2N2/erN27dys7O1v79u2Tu7u7Fi1apK1bt+r+++/X8OHDtX//fp05c0arVq3Sxx9/rLfeektPPPGEpILzuZo1a+bY3qZNm9S/f3/ZbDZNmTJFp06d0vDhwzVy5Eilp6dr/PjxSkhIkCQ98MAD6tq1q9577z39+uuvSkhI0MGDB+Xu7n7ZS1AAqN24VyOAKrFixQqtXbtWLi4ueuutt2S1WmUYhmbOnKkePXooJSVFf/jDH/SXv/xFaWlpjktOPProo+rdu7e8vLyUn5+vhQsXasSIEY7tnjp1Sp6eno4T5aOjo5Wamqo77rjD8dzX11fR0dEKCAhQy5YttWzZMv3888/Kzs5WTk6OXFxclJ6err/+9a+OI2QbN25UVlaW+vfvX+R9rF69Wi1btlRwcLBefPFFNWnSROPGjdOhQ4f0z3/+U5MnT1ZAQIAef/xxvfHGG2ratGkVdRhATUDwAlBj5Ofny2azycPDo8K2mZeX5zga5+HhUaopzYtsNptcXQtOlTUMQ3a73bG+YRiyWCwVVicA50DwAgAAqCKc4wUAAFBFCF4AAABVhOAFAABQRQheAAAAVYTgBQAAUEX+HwrfSfid5AwDAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 720x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#将所有已添加微信的人数进行筛选出来并按跟进次数求和\n",
    "data_add_wx = data_user_source.loc[data_user_source['状态']=='添加成功',:]\n",
    "data_add_wx.insert(6,'辅助列',1)\n",
    "data_add_wx.groupby(['状态','跟进次数'])['辅助列'].sum().plot(figsize = (10,4))\n",
    "len(data_add_wx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "     * 大部分只需跟进3次以内就能获得客户微信"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " # 打开订单表和客户来源表进行合并进行分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_order = pd.read_excel(\"D:\\\\office\\\\data\\\\data\\\\user_order.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>客户_id</th>\n",
       "      <th>订单金额</th>\n",
       "      <th>下单日期</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020_5482788</td>\n",
       "      <td>4798.0</td>\n",
       "      <td>2020-06-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020_9064231</td>\n",
       "      <td>760.0</td>\n",
       "      <td>2020-06-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020_0408111</td>\n",
       "      <td>4284.0</td>\n",
       "      <td>2020-06-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020_8565227</td>\n",
       "      <td>1188.0</td>\n",
       "      <td>2020-06-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020_5043718</td>\n",
       "      <td>476.0</td>\n",
       "      <td>2020-06-30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          客户_id    订单金额       下单日期\n",
       "0  2020_5482788  4798.0 2020-06-30\n",
       "1  2020_9064231   760.0 2020-06-30\n",
       "2  2020_0408111  4284.0 2020-06-30\n",
       "3  2020_8565227  1188.0 2020-06-30\n",
       "4  2020_5043718   476.0 2020-06-30"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_order.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 550 entries, 0 to 549\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Non-Null Count  Dtype         \n",
      "---  ------  --------------  -----         \n",
      " 0   客户_id   550 non-null    object        \n",
      " 1   订单金额    550 non-null    float64       \n",
      " 2   下单日期    550 non-null    datetime64[ns]\n",
      "dtypes: datetime64[ns](1), float64(1), object(1)\n",
      "memory usage: 13.0+ KB\n"
     ]
    }
   ],
   "source": [
    "data_order.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "#删除订单金额为0的活动订单，将其先保存\n",
    "data_active_order = data_order.loc[data_order['订单金额']==0.0,:].copy()\n",
    "data_order= data_order.drop(index=(data_order.loc[(data_order['订单金额']==0.0)].index))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>客户_id</th>\n",
       "      <th>订单金额</th>\n",
       "      <th>下单日期</th>\n",
       "      <th>广告投放渠道</th>\n",
       "      <th>线索_id</th>\n",
       "      <th>跟进次数</th>\n",
       "      <th>获取时间</th>\n",
       "      <th>状态</th>\n",
       "      <th>月份</th>\n",
       "      <th>辅助列</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020_5482788</td>\n",
       "      <td>4798.0</td>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020_5482788</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-06-04</td>\n",
       "      <td>添加成功</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020_5482788</td>\n",
       "      <td>10800.0</td>\n",
       "      <td>2020-06-24</td>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020_5482788</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-06-04</td>\n",
       "      <td>添加成功</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020_5482788</td>\n",
       "      <td>3834.0</td>\n",
       "      <td>2020-06-04</td>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020_5482788</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-06-04</td>\n",
       "      <td>添加成功</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020_9064231</td>\n",
       "      <td>760.0</td>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020_9064231</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-05-30</td>\n",
       "      <td>添加成功</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020_0408111</td>\n",
       "      <td>4284.0</td>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020_0408111</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-06-29</td>\n",
       "      <td>添加成功</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          客户_id     订单金额       下单日期     广告投放渠道         线索_id  跟进次数       获取时间  \\\n",
       "0  2020_5482788   4798.0 2020-06-30  2020_1001  2020_5482788     2 2020-06-04   \n",
       "1  2020_5482788  10800.0 2020-06-24  2020_1001  2020_5482788     2 2020-06-04   \n",
       "2  2020_5482788   3834.0 2020-06-04  2020_1001  2020_5482788     2 2020-06-04   \n",
       "3  2020_9064231    760.0 2020-06-30  2020_1001  2020_9064231     1 2020-05-30   \n",
       "4  2020_0408111   4284.0 2020-06-30  2020_1001  2020_0408111     1 2020-06-29   \n",
       "\n",
       "     状态  月份  辅助列  \n",
       "0  添加成功   6    1  \n",
       "1  添加成功   6    1  \n",
       "2  添加成功   6    1  \n",
       "3  添加成功   5    1  \n",
       "4  添加成功   6    1  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#以添加微信的客户和订单表合并\n",
    "data_orders = pd.merge(data_order,data_add_wx,left_on ='客户_id',right_on = '线索_id')\n",
    "data_orders.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>客户_id</th>\n",
       "      <th>订单金额</th>\n",
       "      <th>下单日期</th>\n",
       "      <th>广告投放渠道</th>\n",
       "      <th>获取时间</th>\n",
       "      <th>辅助列</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020_5482788</td>\n",
       "      <td>4798.0</td>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020-06-04</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020_5482788</td>\n",
       "      <td>10800.0</td>\n",
       "      <td>2020-06-24</td>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020-06-04</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020_5482788</td>\n",
       "      <td>3834.0</td>\n",
       "      <td>2020-06-04</td>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020-06-04</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020_9064231</td>\n",
       "      <td>760.0</td>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020-05-30</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020_0408111</td>\n",
       "      <td>4284.0</td>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020-06-29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          客户_id     订单金额       下单日期     广告投放渠道       获取时间  辅助列\n",
       "0  2020_5482788   4798.0 2020-06-30  2020_1001 2020-06-04    1\n",
       "1  2020_5482788  10800.0 2020-06-24  2020_1001 2020-06-04    1\n",
       "2  2020_5482788   3834.0 2020-06-04  2020_1001 2020-06-04    1\n",
       "3  2020_9064231    760.0 2020-06-30  2020_1001 2020-05-30    1\n",
       "4  2020_0408111   4284.0 2020-06-30  2020_1001 2020-06-29    1"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#剔除多余列\n",
    "data_orders = data_orders[['客户_id','订单金额','下单日期','广告投放渠道','获取时间','辅助列']]\n",
    "data_orders.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  提取所有客户第一次下单的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>客户_id</th>\n",
       "      <th>下单日期</th>\n",
       "      <th>订单金额</th>\n",
       "      <th>广告投放渠道</th>\n",
       "      <th>获取时间</th>\n",
       "      <th>辅助列</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020_0030076</td>\n",
       "      <td>2020-06-13</td>\n",
       "      <td>7302.0</td>\n",
       "      <td>2020_1002</td>\n",
       "      <td>2020-06-13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020_0031020</td>\n",
       "      <td>2020-06-10</td>\n",
       "      <td>500.0</td>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020-06-07</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020_0033778</td>\n",
       "      <td>2020-06-29</td>\n",
       "      <td>2520.0</td>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020-06-13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020_0043298</td>\n",
       "      <td>2020-06-07</td>\n",
       "      <td>13080.0</td>\n",
       "      <td>2020_1001</td>\n",
       "      <td>2020-05-31</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020_0061360</td>\n",
       "      <td>2020-06-28</td>\n",
       "      <td>3564.0</td>\n",
       "      <td>2020_1002</td>\n",
       "      <td>2020-06-18</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          客户_id       下单日期     订单金额     广告投放渠道       获取时间  辅助列\n",
       "0  2020_0030076 2020-06-13   7302.0  2020_1002 2020-06-13    1\n",
       "1  2020_0031020 2020-06-10    500.0  2020_1001 2020-06-07    1\n",
       "2  2020_0033778 2020-06-29   2520.0  2020_1001 2020-06-13    1\n",
       "3  2020_0043298 2020-06-07  13080.0  2020_1001 2020-05-31    1\n",
       "4  2020_0061360 2020-06-28   3564.0  2020_1002 2020-06-18    1"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#获取所有客户第一次购买的时间\n",
    "data_order_one = data_orders.groupby(['客户_id','下单日期'])[['辅助列']].size().reset_index()\n",
    "#进行聚合后，多次购买的用户将会按日期排序，利用drop_duplicates保留第一个出现的值，可以获取客户第一次下单时间\n",
    "data_order_one.drop_duplicates('客户_id',inplace = True)\n",
    "data_order_one = data_order_one[['客户_id','下单日期']]\n",
    "#订单表和第一次购买行为信息的合并\n",
    "data_order_one = pd.merge(data_order_one,data_orders)\n",
    "data_order_one.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "day\n",
       "8     10\n",
       "7     10\n",
       "3     10\n",
       "2     36\n",
       "1    128\n",
       "Name: 辅助列, dtype: int64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAlIAAADMCAYAAAC4LE1pAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAAgAElEQVR4nO3de3iU5Z3/8U8yOTOTSSAnGJSzIIkCIrjUWPGwYsKqgFrYeMVWSxEPXdoia822HlZXa7e9atHFllp0ibJA+SlQRUCrFEUaIIajJAISDQmnnAmTycxk5vdHDoIiZCbPZJLM+3VdXkmezOGLDzPz4b7v5/6Geb1erwAAAOCz8GAXAAAA0FMRpAAAAPxEkAIAAPATQQoAAMBPBCkAAAA/EaQAAAD8FBGMJ73qqqtks9mC8dQAAAA+KS8vV0FBwTl/F5QgZbPZ9MYbbwTjqQEAAHwyY8aMb/0dU3sAAAB+IkgBAAD4iSAFAADgJ4IUAACAn3plkDrlcOnF9w/I3ewJdikAAKAX65VBqujLWv1m42fadaQ22KUAAIBerFcGqb59oiRJJ085g1wJAADozXplkEq2REuSKhuaglwJAADozXplkPpqRIogBQAAAqdXBqlIU7gS4yIZkQIAAAHVK4OUJCWZowlSAAAgoHptkEq2RKuygcXmAAAgcDoUpCorK5WTkyNJqqioUG5uru6++2798pe/lNfrlcvl0ty5czVr1iytWrUqoAV3VJI5mjVSAAAgoC4YpOrq6vTII4+osbFRkrRixQo98cQTWrp0qY4ePaqSkhK99tprSk9P1/Lly7VhwwY1NDQEvPALYWoPAAAE2gWDlMlk0vPPPy+z2SxJ+ulPf6phw4ZJkmpra5WYmKiCggJlZWVJkiZMmKC9e/cGsOSOSbJEye5slt3pDnYpAACgl7pgkDKbzbJYLN84vm7dOo0YMUKpqalqbGxUamqqJMlqtaqqqsr4Sn2UbG7dS4pNOQEAQID4tdi8rKxMf/7zn5WXlydJiouLk8PhkCTZ7XZ5PMHvcZfUuinnyQZHkCsBAAC9lc9Bqq6uTj/72c/0zDPPtI9Upaenq7CwUJJUXFwsm81mbJV+aBuRok0MAAAIlAhf77B48WIdPXpUTz/9tCTpxz/+saZPn645c+Zox44dOnjwoMaMGWN4ob5KMtMmBgAABFaHg1R+fr4kacGCBVqwYME3fr9kyRIVFhZq3rx5MplMxlXop37mljYxBCkAABAoPo9IfZvU1FRlZ2cb9XCd1tYmhr2kAABAoPTanc0l9pICAACBFQJBisXmAAAgMHp1kGrpt8eIFAAACIxeHaTotwcAAAKpdwcp2sQAAIAA6t1BijYxAAAggHp1kEpubxPD9B4AADBe7w5S7W1iCFIAAMB4vTpI0SYGAAAEUq8OUrSJAQAAgdSrg1RbmxiCFAAACIReHaQk9pICAACBExJBijYxAAAgEHp/kKJNDAAACJAOBanKykrl5ORIklwul+bOnatZs2Zp1apV33qsu0g2R6uSqT0AABAAFwxSdXV1euSRR9TY2ChJeu2115Senq7ly5drw4YNamhoOOex7iLJEqXTtIkBAAABcMEgZTKZ9Pzzz8tsNkuSCgoKlJWVJUmaMGGC9u7de85j3QVtYgAAQKBcMEiZzWZZLJb2nxsbG5WamipJslqtqqqqOuex7qJ9d3PWSQEAAIP5vNg8Li5ODodDkmS32+XxeM55rLto67fHgnMAAGA0n4NUenq6CgsLJUnFxcWy2WznPNZdJNFvDwAABEiEr3eYPn265syZox07dujgwYMaM2aMUlNTv3Gsu6BNDAAACJQOj0jl5+dLkmw2m5YsWaIrrrhCr776qkwm0zmPdReRpnAl0CYGAAAEgM8jUpKUmpqq7OzsCx7rLlr2kuKqPQAAYKxev7O51NpvjxEpAABgsNAIUrSJAQAAARAaQcocRZsYAABguJAIUsmWaNrEAAAAw4VEkKJNDAAACISQCFK0iQEAAIEQEkGqfUSKIAUAAAwUEkGKfnsAACAQQiJItbWJod8eAAAwUkgEKdrEAACAQAiJICW1rJPiqj0AAGCkkAlSyWZ2NwcAAMYKmSCVZKHfHgAAMFboBCnaxAAAAIOFUJBqaRPT6GwOdikAAKCX8DlI1dXV6Uc/+pFmzJihxx57TJKUl5enmTNnatGiRYYXaBT2kgIAAEbzOUitWbNGt9xyi9544w2dPn1af/rTn+TxeLRixQqVlZWptLQ0AGV2XlubmBNM7wEAAIP4HKQSEhJ04MAB1dfX6+jRozpy5IiysrIkSZmZmSosLDS8SCPQJgYAABjN5yA1fvx4VVRUaOnSpRo2bJhcLpdSU1MlSVarVVVVVYYXaYQkS8vu5gQpAABgFJ+D1Isvvqgnn3xSDz30kIYOHaq33npLDodDkmS32+XxeAwv0gj9+rSOSLEpJwAAMIjPQaq+vl4lJSVqbm7Wrl27NGfOnPbpvOLiYtlsNsOLNEJUREubmJMNjmCXAgAAeokIX+9w33336dFHH1VFRYXGjh2rH/zgB8rJydGJEye0efNmrVy5MhB1GoI2MQAAwEg+B6nLL79cb7/99lnH8vPztWXLFs2ePVsWi8Ww4oyWZI5ijRQAADCMz0HqXKxWq7Kzs414qIBKtsRoz5HaYJcBAAB6iZDZ2VxqGZE6yT5SAADAICEWpGgTAwAAjBNSQSqZTTkBAICBQitItfbbO0mQAgAABgipINXWJoZ1UgAAwAihFaRoEwMAAAwUUkGKNjEAAMBIIRWk2trEMCIFAACMEFJBSmpZJ8UaKQAAYIQQDFK0iQEAAMYIwSAVTZACAACGCLkglWyJVmUDi80BAEDnhVyQSjJHq6HJTZsYAADQaSEXpGgTAwAAjOJ3kHriiSf0/vvvS5Ly8vI0c+ZMLVq0yLDCAqVtU07axAAAgM7yK0jt2LFDlZWVuv7667Vx40Z5PB6tWLFCZWVlKi0tNbhEYyWbYyRJlWyBAAAAOsnnIOVyufSLX/xCNptN7733nrZt26asrCxJUmZmpgoLCw0v0kiMSAEAAKP4HKRWr16t4cOHa/bs2dqzZ49ef/11paamSpKsVquqqqoML9JItIkBAABGifD1Dvv379f3vvc9JScn69Zbb9Unn3wih8MhSbLb7fJ4PIYXaaSoiHBZY2kTAwAAOs/nEamLL75YZWVlkqQ9e/aovLy8fTqvuLhYNpvN2AoDoGUvKYIUAADoHJ9HpO644w7l5eVp3bp1crvdys/P1/33368TJ05o8+bNWrlyZSDqNFSSOYp+ewAAoNN8DlJms1kLFy4861h+fr62bNmi2bNny2KxGFZcoCSZo7W3vC7YZQAAgB7O5yB1LlarVdnZ2UY8VJdo6bfHYnMAANA5IbezudSyRoo2MQAAoLNCM0jRJgYAABggJIMUm3ICAAAjhGaQahuR4so9AADQCSEZpJItbVN7LDgHAAD+C8kg1dYmhr2kAABAZ4RkkKJNDAAAMEJIBimpZXdzghQAAOiMkA1S9NsDAACdFbJBKskczRopAADQKSEdpLhqDwAAdEbIBqm2NjEOF21iAACAf0I3SJnZAgEAAHROyAYp2sQAAIDO8jtIVVZWatq0aZKkvLw8zZw5U4sWLTKssECjTQwAAOgsv4PUc889J4fDoY0bN8rj8WjFihUqKytTaWmpgeUFTnuQYsE5AADwk19BauvWrYqNjVVycrK2bdumrKwsSVJmZqYKCwsNLTBQ+plbpvbYSwoAAPjL5yDldDq1aNEiPfzww5Iku92u1NRUSZLValVVVZWxFQZIdIRJ1thIFpsDAAC/+RykFi9erJycHMXHx0uS4uLi5HA4JLWEKo/HY2yFAUSbGAAA0BkRvt5h69atKigo0LJly7R//35VVFSof//+Gjt2rIqLizVkyJBA1BkQLZtyEqQAAIB/fA5Sr7/+evv3ubm5eumll5STk6MTJ05o8+bNWrlypaEFBlKyJVr7KuqDXQYAAOihOrWPVH5+vsxms/Lz8zVmzBgtXbpUFovFqNoCLi0+RhW1jexuDgAA/GLIhpxWq1XZ2dlKTk424uG6zNUjktTk9ujjQ5XBLgUAAPRAIbuzuSRdPSxJlugIrd97LNilAACAHiikg1RURLhuuDRF7356XO7mnnO1IQAA6B5COkhJ0s0Zaaqxu7TtcHWwSwEAAD1MyAepay9JUUxkuNbvY3oPAAD4JuSDVGyUSZMvSdGGfcfk8XiDXQ4AAOhBQj5ISS3Te8frm7TzSG2wSwEAAD0IQUrSdaNSFGkK4+o9AADgE4KUJGtspK4enqT1e4/J62V6DwAAdAxBqtXN6Wn6stqu/UdPBbsUAADQQxCkWt04OlXhYdL6vUeDXQoAAOghCFKtkszRmjC4L9sgAACADiNInSErI02fHW/QoZMNwS4FAAD0AASpM9yUniZJ2sCoFAAA6ACC1BkGJMRqzEUJbIMAAAA6hCD1NTenp2n3kTqV1zYGuxQAANDN+RykTp06pdmzZ+vee+/Vgw8+KKfTqby8PM2cOVOLFi0KRI1d6uaM1uk9RqUAAMAF+Byk1q5dq3vuuUdLlixRUlKS1q1bJ4/HoxUrVqisrEylpaUBKLPrDEnqo1FpFq7eAwAAF+RzkLrrrrt09dVXS5Jqamq0du1aZWVlSZIyMzNVWFhobIVBMCU9TdtLq3XyVFOwSwEAAN2Y32ukioqKVFdXp7S0NKWmpkqSrFarqqqqDCsuWLIuS5PXK7376fFglwIAALoxv4JUbW2tnnrqKT3zzDOKi4uTw+GQJNntdnk8HkMLDIaRqRYN7hfH9B4AADgvn4OU0+nUvHnzNH/+fNlsNmVkZLRP5xUXF8tmsxleZFcLCwvTlIw0fXywUnV2V7DLAQAA3ZTPQWrVqlX69NNP9Yc//EG5ubnyer1as2aNnn32Wb3zzjuaPHlyAMrsejenp8nt8epvxUzvAQCAc4vw9Q45OTnKyck569j111+vLVu2aPbs2bJYLIYVF0xjBiaovzVG6/ce04wrBga7HAAA0A0ZsiGn1WpVdna2kpOTjXi4biE8PExT0tP0989Oyu50B7scAADQDbGz+XlMSU9Tk9ujTSUng10KAADohghS5zFhcKL69omi9x4AADgngtR5RJjCddPoVL1ffEJN7uZglwMAALoZgtQFTMlIU0OTWx8f7PkbjQIAAGP5fNVeqPnOsH6yREco7809Sh9g1cDEWNkSYjUgIVa2xFgNSIhRsjlaYWFhXV5bZUOTHl+zT7vLay942/CwMGVf1l8PXTdcfaI57QAAGIFP1AuIjjDp8VvT9c6eozpSY1fB51U61XT2VXxREeGt4SpGtoRY2RLiWr5PjNXAhDilWWMUFWHs4N/fPzup+St3qd7h0pT0NEWGnz/I1Ta69NKmQ1pdVK5fTB2t7MvSghL+AADoTQhSHXDH+IG6Y/xXe0nVO1wqr2lUeU2jKupavh6pbfm6qeSkTnyt2XFYmJRiiW4ZxUqI1eB+fXTnlQM1qF8fn2tpcjfr1+tL9OePDuuSVLNemz1Ro9LiO3Tfwi+q9cvV+/Tgsk909fB+evLWdA1P6fp9vxqdze3/3ypqG3W8vknfvSRJ4y5O7PJaAADojDCv1+vt6iedMWOG3njjja5+2i7T5G7WsTpHS9iqbf3vzNBV0yiP16tbxwzQ/ZOHa2Rax8LMwROn9OP/26n9R+t196RBysu+VDGRJp9qa/Z49XrBF/rNhhLZnc36YeYQ/fiGETIHYLpv95FabS+taQ9M5bUtX6tOO895++9dOVCP3DxK/czRhtfydSXHTulw5emWEcTEWCXGRQZ8hK608rQOV53WpKH9fD5v/nI1e/TRwcoufU4A6G3Ol1sYkQqA6AiTBvXr860jTifqHfrTh5/r9YIvtXpnhW4anaoHrxuuMRclnPP2Xq9Xy7Z9qafe+lRxURF6+e4rdePoVL9qM4WH6e5Jg5V9WX/9en2x/rj5c63eWa7/mDpat1zev9Nhwuv1auuhKr34wUF9fKhlgX5spKl1PVmsMmxW2VqnPdumQC3RkfqfTQe15KPDWr/3mBZMGamcqwbJdIHpSn/U2p367w0lWrbtS535T4jYSFPrdGxcS31ta+Cssbp0QLziYyL9er6qhia9tfuoVu8sV9GXLWvZzNERyspI0/RxNl01tF9A/pyS9GWVXf+2vEg7y2o1MtWihf86rsOhvTtxN3t0yuFWYp+oYJcCAN/AiFQQ1Zx26pWPS/XqlsOqd7h1zYgkPTB5uP5paN/2QFNz2qlH/t9ubfz0uK4ZkaTf3jlGKfExhtVQ+EWNHluzV/sq6jVpaD89eVu6Lkn1/cPW6/Xqb/tP6MUPDmpnWa1SLNH60TVDNf0Km/r1iepQQDtw/JQeX7tPHx+qUvqAeP3nbRkaP8iY6T6Px6sVO8r06/XFqne4dfekQbptrK1l5LB1pOzMUcMzR83CwqRL0+I1cUhfTRzSVxMG91Wy5dtHzRqdzXp3/3GtLirX5s9Oyu3xalSaRdPH2XRJqkXr9hzVO3uPqaHJrbT4GN02doCmjbPp0v4dm6LtiDeLjuiXq/cpLEz60TVDtXRrqeodbv1H9qW6e9KgHrM+ruDzKj22Zp9Kjp/SHeMH6udZo5TUBSOWAHCm8+UWglQ3cMrh0usFX+rlDw+rsqFJ4wcl6qHrhisqIlw/W7lT1aed+vcpo/TDzCEKD8DoRbOnZcTrNxtKVO9waVRavCYOTtTEIf00YUiiUizfHtyaPV69veeoFn1wUMXHTmlgYqzmXjtMd4wf6NdUktfb8nhPv7Vfx+odhnx47iqr1WNr9mrXkTpNHNxXT96WfsHQ0raOq6zarp1ltdpeWq1PvqhVo6tlP7EhSX00ofX/0cTBfWVLjNU/Pq/Sm0XlWt8akvpbY3TbWJumjRvwjXVsDlez3msNW5tKvgpb08bZdOuYARqQEOvXn7Xe4dJjq/dq9c4KTRicqN/NHKuBiXE6eapJC1bt0qaSk7phVIp+fcflXTKF6q8T9Q49s26/Vu+skC0hVt+9JFl/2VGm2CiTHr5ppO666mJFmNi9BUDXIEj1EA5Xs1buKNMf//65ymsbJUlDk/to4axxyrBZA/78VQ1NWlbwpQoOV+uTL2tkd7aEhsH94tpHYiYO6auL+8bJ1ezVm0VH9NKmQyqtsmt4ilkPTB6mW8YMUKQBH3Cnm9x64f2D+vNHnysm0r8Pz5rTTv16Q4mWb/9SSeZo/Uf2pbpt7AC/R2NczR7tLa/T9tJqbTtcox1fVKvW7pIkRUeEq8ntkSU6QtmX9de0cTZdNaRvh4Jv9Wmn3t5zVKuLylX4RY3CwqSrhvTV9HE23ZzRX9bYjk0rFn5Ro5+sKFJFrUPzbhihB68bfta0odfr1f9+XKpn3imWNTZSv71zjL57Scf6Y9qdbm3cd1xrdparye3RLWMGKDujv6xx/k15fhtXs0f/+3Gpnn/vgJxuj+Z8d6gevG64YqNMOniiQU+s3aePDlbq0v7xeuq2dF05uK+hzw8A50KQ6mFczR6t2Vmh4/UO3XP1YMVFdf1SNlezR/sq6rX9cLW2lVZre+lXoSHFEq2wMOl4fZMybPF66Lrhuml0WkBGy8788ByZatGEIYnta6sGtq6zSrZEnxUYmj1eLd/+pf57Q4lOOdz6wXcG6yc3jpDFz3VO38bj8erAiQZtK63WZ8dOadKwfrp+VEqnFnV/UXVaq4sqtGZnuT6vPK2oiHDdeGmKpo21afLIlHNuo9Hs8ep/Pjio3//tgPpbY/T7WePOOyW6/2i9/u3/inTgRINmZw7RgptHKjrimzW7Wxeqry4q18ZPj8vubJYtIVbREeEttZnCdf2oFE0bZ9N1o5LP+Ri+2HqoSo+v3avPjjdo8shkPX5LuoYknb3O0Ov16p29x/TUW5/qaJ1DM66w6dGsS8871QrAf16vV85mjxwuj5rczWpyeeRwNbf/7Gj7ue13ZxxrcnvU5Gpu/77tfrFRJl0xKFETB/fViBRzQD47jEaQQqd5PF4dPNmgbYdbQlWDw627vzNY3x2RFPD1Nm0fnn/8+yF9UW1vD3RtIk1hSrPGtG+UeuB4g/aU1+mqIX31n7dl9MgF1l6vV7uP1OnNonL9dVeFqk47ZY2N1NTL+2v6OJvGX5yo8PAwldc26qfLd2pbabVuGztAT03L6NDCeIerWf/19n7l/+MLje4fr4X/Ok7DU8zyer3aU/7V81Y2OBUfE6Gplw/Q9HE2XTkoUWFhOuM2R1XZ0NR+m2ljB2jC4I6NxLU5VufQf63br7/uqtDAxFg99i+j9c+jU8/798rubBmxfPnDzxUTYdLPbrpEuf80qMdN9zndHh1tXZfXslbP0T59fD7hYVLyGVuqDEyMlTU28FeeIri8Xu9ZgeSsIHNmWHF/M8h8M/i0/q7ttl8LO2cGo86khJjIcEVHmBQTGa6YSJNiIkyqsTvbtwlKiIvUlYP6auKQRE0Y3FcZNqshsxpG65IglZeXp0OHDunaa6/VAw884HdBwIU0NLl1tParvbsqztxiorZREaZwzb/pEt06xv9pvO7EdcbI0IZ9x+RweTQwMVY3jErRG0Xl8ni8enp6hqaPG3jhB/uadz89rn9ftUuNrmZ978qL9NHBSn1+suOjTe5mj7YcqtLq1rVhja6WUavbxg7Q8BTzBZ//SE2j/vj3Q3J5vJp77TA9MHmYTyN6h062jFh+eKBSo9Is+sF3Bhu++a1RTjncqmj9e9t2ccPJhqZvfEh1pH6Pxyu35+w7xkWZzuq6YEuIVcrXRmvR9bxeye3xnDVKc66w0nS+UZ7WwON0e/yuIzxMLUEm0qToiPBzfm0LO1/9bFJMRLiiv3bsW2/fGpiiI0yKjgxXdET4Od+DvV6vyqobVXC4SttLq7W9tEaHK09LarmC+opBCZowuK8uSozThd7CI03h+ufRqQHf3iXgQWrjxo16//339atf/UqPPvqo7rvvPg0ePNivggB8u4YmtzbuO6bVOyv00YGTunxggn4/a6xfm7u2OV7v0PyVu/TRwcr2tVlZfqx/Ot3k1rufHtebReX68MBJeTr4znLDqBQ9dstov/8MXq9XG/Yd01Nv7W9fW9hdRZnC27setIeeM4JPmjWmQ1OkXq9X1aedqqh1qLzWriM1je3ft41sVX/Lfm0IvojwsPYg0hY6zgwh7aM3Z4ad1tt8ddtz3f6rn6O/dptIU1i3/ofliVMObT9c07oGtVr7j9V3eCTsj7njNSU9LaD1BTxIPf3007rmmmt07bXX6u2335bD4dDtt9/uV0EAOsbudCs20mTIm6PX61Wjq9mw9Xi1dqfqGl0XvF2kKdzvKxS/rm2arLuKjTIpqU90l60HsTvdqjzllFddvnoDXxNhClfMGaM/PW0KOhjqHS7VdOAfA0a+h5xPwDfktNvtSk1t2SDSarWqvLzciIcFcB5GXoQQFhZm6OMlxEUpIa5rN9CMigjv1MhcbxMXFaGL+7HnMnqm+JhIvzdC7mqGxOK4uDg5HA5JLaHK4/F/HhcAAKCnMCRIZWRkqLCwUJJUXFwsm81mxMMCAAB0a4aM+954443KycnRiRMntHnzZq1cudKIhwUAAOjWDBmRMpvNys/P15gxY7R06VJZLD1v3x4AAABfGbYS0Wq1Kjs726iHAwAA6PaCcklHeXm5ZsyYEYynBgAA8Mn5diMISosYAACA3oBdwQAAAPxEkAIAAPATQQoAAMBPBCkAAAA/EaQAAAD8RJACAB/V1tZqy5Ytqq6uDnYpAIKsxwWpvLw8zZw5U4sWLQp2KSGnsrJSOTk5kiSXy6W5c+dq1qxZWrVqVZArCw2nTp3S7Nmzde+99+rBBx+U0+nk9RAEdXV1mjt3rnbv3q3vf//7qq6u5jwESWVlpaZNmyaJz4ZgcLvdmjx5snJzc5Wbm6uSkhItXLhQt99+u5588slgl9dlelSQ2rhxozwej1asWKGysjKVlpYGu6SQUVdXp0ceeUSNjY2SpNdee03p6elavny5NmzYoIaGhiBX2PutXbtW99xzj5YsWaKkpCStW7eO10MQlJSU6Oc//7nuv/9+ZWZm6h//+AfnIUiee+45ORwOPhuCpKSkRFOnTlV+fr7y8/PlcrlUWFioVatWqV+/fvr444+DXWKX6FFBatu2bcrKypIkZWZmqrCwMMgVhQ6TyaTnn39eZrNZklRQUNB+LiZMmKC9e/cGs7yQcNddd+nqq6+WJNXU1Gjt2rW8HoJg4sSJGjt2rLZv367du3frww8/5DwEwdatWxUbG6vk5GQ+G4Jk586d2rRpk+644w7l5eVp69atuummmxQWFqbMzEzt2LEj2CV2iR4VpOx2u1JTUyW19ParqqoKckWhw2w2n9WMurGxkXMRJEVFRaqrq1NaWhrnIEi8Xq/WrVun+Ph4hYWFcR66mNPp1KJFi/Twww9L4rMhWC677DK98sorWrVqldxut5qams46D5WVlUGusGv0qCAVFxcnh8MhqeWF4/F4glxR6OJcBEdtba2eeuopPfPMM5yDIAoLC9Pjjz+ukSNHqqioiPPQxRYvXqycnBzFx8dL4v0oWEaNGqWUlBRJUkZGhuLi4tTU1CSp5TyESge6HhWkMjIy2odsi4uLZbPZglxR6EpPT+dcdDGn06l58+Zp/vz5stlsvB6CZPHixVq9erWklgsA5syZw3noYlu3btWyZcuUm5ur/fv364MPPuAcBMGCBQtUXFys5uZmvffee7Lb7SF5HnpU0+KGhgbl5ORo0qRJ2rx5s1auXHnWdBMCLzc3V/n5+SovL9ecOXM0adIkFRUVaeXKlTKZTMEur1dbtmyZfve732nUqFGSpBkzZuiVV17h9dDF6urq9JOf/EROp1MjRozQ/Pnzddddd3EegiQ3N1cvvfQSnw1B8Nlnn2n+/PmSpOuvv17z5s1TTk6OMjIy9OGHH+rll1/WRRddFOQqA69HBSmp5U1sy5YtmjBhgpKTk4NdTkg7fvy4CgsLdc011/CmFSS8HroHzkPwcQ66B4fDoU2bNik9PT0kQpTUA4MUAABAd9Gj1kgBAKlYGLIAAAGQSURBVAB0JwQpAAAAPxGkAPRYL7zwggoKCoJdBoAQRpACAADwU0SwCwAAX9TV1WnevHlqbm6W1LK/3A9/+EM1NjZq0KBBevbZZ7Vw4UINGzZMU6dO1QsvvKChQ4dq6tSpQa4cQG/EiBSAHmXFihWaPHmy8vPzFRERoZMnTyo3N1evvvqqjhw5osrKSk2bNk1//etfJUkfffSRbrjhhiBXDaC3IkgB6FGOHDnSvilpRkaGIiIi9Je//EUPP/yw6urq5HA4dPHFF+v06dMqKCjQiBEjFBMTE+SqAfRWBCkAPcqAAQN08OBBSdL+/fu1atUqTZkyRb/97W8VFxfXfrupU6cqLy9P06ZNC1apAEIAG3IC6FGqq6s1b948SZLL5VJmZqbWr1+v+Ph4eTweLViwQOPHj1dNTY3uvPNOvfvuuwoLCwty1QB6K4IUgF7nwIEDevTRRzVz5kzdeeedwS4HQC9GkAIAAPATa6QAAAD8RJACAADwE0EKAADATwQpAAAAPxGkAAAA/ESQAgAA8NP/Byd2eFHj/xQJAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 720x216 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#查看客户添加微信后多久会第一次下单\n",
    "data_order_one['day']= (data_order_one['下单日期'] - data_order_one['获取时间'])\n",
    "# 转成int类型，添加第一天就下单日期相减为0,算第一天，加个1\n",
    "data_order_one['day'] = data_order_one['day'].dt.days + 1\n",
    "data_order_one.groupby('day')['辅助列'].sum().sort_index().plot(figsize = (10,3))\n",
    "data_order_one.groupby('day')['辅助列'].sum().sort_values()[-5:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "  *      加到客户微信后前两天成交的可能性最大"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 计算客户开发率和复购率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "客户开发率 0.031\n"
     ]
    }
   ],
   "source": [
    "#获取新单成交率\n",
    "print(\"客户开发率\" , round(len(data_order_one) / len(data_add_wx),3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "客户复购率 0.122\n"
     ]
    }
   ],
   "source": [
    "#获取客户复购率\n",
    "data_RePurchase = data_orders.groupby('客户_id')['辅助列'].sum().reset_index()\n",
    "print(\"客户复购率\" , round(len(data_RePurchase.loc[data_RePurchase['辅助列'] >=2,:]) / len(data_order_one),3))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " * 两个月销售数据的客户开发率为3%，复购率为12%"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 计算各销售价格区间单数和占比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1000-2000元      133\n",
       "2000-5000元      108\n",
       "10000-50000元     43\n",
       "5000-10000元      38\n",
       "0-1000元          32\n",
       "500000元以上         1\n",
       "Name: 订单金额, dtype: int64"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_cut = pd.cut(data_orders['订单金额'],bins = [0,1000,2000,5000,10000,50000,200000],\n",
    "                   labels = ['0-1000元','1000-2000元','2000-5000元','5000-10000元','10000-50000元','500000元以上'])\n",
    "price_num = price_cut.value_counts()\n",
    "price_num\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "price_cut\n",
       "0-1000元          19180.0\n",
       "1000-2000元      183429.4\n",
       "2000-5000元      367656.0\n",
       "5000-10000元     261090.0\n",
       "10000-50000元    851584.0\n",
       "500000元以上        56280.0\n",
       "Name: 订单金额, dtype: float64"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_orders['price_cut'] = price_cut\n",
    "cut_purchase = data_orders.groupby('price_cut')['订单金额'].sum()\n",
    "cut_purchase"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<script>\n",
       "    require.config({\n",
       "        paths: {\n",
       "            'echarts':'https://assets.pyecharts.org/assets/echarts.min'\n",
       "        }\n",
       "    });\n",
       "</script>\n",
       "\n",
       "        <div id=\"d1454751abc94a4d85f925270a7be599\" style=\"width:900px; height:500px;\"></div>\n",
       "\n",
       "<script>\n",
       "        require(['echarts'], function(echarts) {\n",
       "                var chart_d1454751abc94a4d85f925270a7be599 = echarts.init(\n",
       "                    document.getElementById('d1454751abc94a4d85f925270a7be599'), 'white', {renderer: 'canvas'});\n",
       "                var option_d1454751abc94a4d85f925270a7be599 = {\n",
       "    \"animation\": true,\n",
       "    \"animationThreshold\": 2000,\n",
       "    \"animationDuration\": 1000,\n",
       "    \"animationEasing\": \"cubicOut\",\n",
       "    \"animationDelay\": 0,\n",
       "    \"animationDurationUpdate\": 300,\n",
       "    \"animationEasingUpdate\": \"cubicOut\",\n",
       "    \"animationDelayUpdate\": 0,\n",
       "    \"color\": [\n",
       "        \"#EF9050\",\n",
       "        \"#3B7BA9\",\n",
       "        \"#6FB27C\",\n",
       "        \"#FFAF34\",\n",
       "        \"#D8BFD8\",\n",
       "        \"#00BFFF\",\n",
       "        \"#7FFFAA\"\n",
       "    ],\n",
       "    \"series\": [\n",
       "        {\n",
       "            \"type\": \"pie\",\n",
       "            \"clockwise\": true,\n",
       "            \"data\": [\n",
       "                {\n",
       "                    \"name\": \"0-1000\\u5143\",\n",
       "                    \"value\": 19180.0\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"1000-2000\\u5143\",\n",
       "                    \"value\": 183429.4\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"2000-5000\\u5143\",\n",
       "                    \"value\": 367656.0\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"5000-10000\\u5143\",\n",
       "                    \"value\": 261090.0\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"10000-50000\\u5143\",\n",
       "                    \"value\": 851584.0\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"500000\\u5143\\u4ee5\\u4e0a\",\n",
       "                    \"value\": 56280.0\n",
       "                }\n",
       "            ],\n",
       "            \"radius\": [\n",
       "                \"35%\",\n",
       "                \"60%\"\n",
       "            ],\n",
       "            \"center\": [\n",
       "                \"50%\",\n",
       "                \"50%\"\n",
       "            ],\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8,\n",
       "                \"formatter\": \"{b}:{d}%\"\n",
       "            },\n",
       "            \"rippleEffect\": {\n",
       "                \"show\": true,\n",
       "                \"brushType\": \"stroke\",\n",
       "                \"scale\": 2.5,\n",
       "                \"period\": 4\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"legend\": [\n",
       "        {\n",
       "            \"data\": [\n",
       "                \"0-1000\\u5143\",\n",
       "                \"1000-2000\\u5143\",\n",
       "                \"2000-5000\\u5143\",\n",
       "                \"5000-10000\\u5143\",\n",
       "                \"10000-50000\\u5143\",\n",
       "                \"500000\\u5143\\u4ee5\\u4e0a\"\n",
       "            ],\n",
       "            \"selected\": {},\n",
       "            \"show\": true,\n",
       "            \"left\": \"2%\",\n",
       "            \"top\": \"15%\",\n",
       "            \"orient\": \"vertical\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10,\n",
       "            \"itemWidth\": 25,\n",
       "            \"itemHeight\": 14\n",
       "        }\n",
       "    ],\n",
       "    \"tooltip\": {\n",
       "        \"show\": true,\n",
       "        \"trigger\": \"item\",\n",
       "        \"triggerOn\": \"mousemove|click\",\n",
       "        \"axisPointer\": {\n",
       "            \"type\": \"line\"\n",
       "        },\n",
       "        \"textStyle\": {\n",
       "            \"fontSize\": 14\n",
       "        },\n",
       "        \"borderWidth\": 0\n",
       "    },\n",
       "    \"title\": [\n",
       "        {\n",
       "            \"text\": \"\\u4e0d\\u540c\\u4ef7\\u683c\\u533a\\u95f4\\u7684\\u9500\\u552e\\u989d\\u6574\\u4f53\\u8868\\u73b0\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10\n",
       "        }\n",
       "    ]\n",
       "};\n",
       "                chart_d1454751abc94a4d85f925270a7be599.setOption(option_d1454751abc94a4d85f925270a7be599);\n",
       "        });\n",
       "    </script>\n"
      ],
      "text/plain": [
       "<pyecharts.render.display.HTML at 0x1b05bdc2088>"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_pair = [list(z) for z in zip(cut_purchase.index.tolist(), cut_purchase.values.tolist())]\n",
    "# 绘制饼图\n",
    "pie1 = Pie() \n",
    "pie1.add('', data_pair, radius=['35%', '60%'])\n",
    "pie1.set_global_opts(title_opts=opts.TitleOpts(title='不同价格区间的销售额整体表现'), \n",
    "                     legend_opts=opts.LegendOpts(orient='vertical', pos_top='15%', pos_left='2%'))\n",
    "pie1.set_series_opts(label_opts=opts.LabelOpts(formatter=\"{b}:{d}%\"))\n",
    "pie1.set_colors(['#EF9050', '#3B7BA9', '#6FB27C', '#FFAF34', '#D8BFD8', '#00BFFF', '#7FFFAA'])\n",
    "pie1.render_notebook() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "                  * 下单金额大于一万的单的数量以百分之9的占比占据了一半的销售额，这样的客户需要重点关注"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4 entries, 0 to 3\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   广告投放渠道  4 non-null      object \n",
      " 1   月份      4 non-null      int64  \n",
      " 2   广告成本    4 non-null      float64\n",
      "dtypes: float64(1), int64(1), object(1)\n",
      "memory usage: 224.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "data_adv_cost = pd.read_excel(\"D:\\\\office\\\\data\\\\data\\\\advertisement_cost.xlsx\")\n",
    "data_adv_cost.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  两个广告渠道各项数据对比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>广告投放渠道</th>\n",
       "      <th>月份</th>\n",
       "      <th>线索数量</th>\n",
       "      <th>广告成本</th>\n",
       "      <th>客户数量</th>\n",
       "      <th>订单金额</th>\n",
       "      <th>订单数量</th>\n",
       "      <th>线索成本</th>\n",
       "      <th>获客成本</th>\n",
       "      <th>ROI</th>\n",
       "      <th>客单价</th>\n",
       "      <th>线索转化率</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020_1001</td>\n",
       "      <td>5</td>\n",
       "      <td>4736</td>\n",
       "      <td>416327.06</td>\n",
       "      <td>3386</td>\n",
       "      <td>204751.4</td>\n",
       "      <td>70</td>\n",
       "      <td>87.906896</td>\n",
       "      <td>122.955422</td>\n",
       "      <td>0.491804</td>\n",
       "      <td>2925.020000</td>\n",
       "      <td>0.714949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020_1002</td>\n",
       "      <td>5</td>\n",
       "      <td>155</td>\n",
       "      <td>30459.90</td>\n",
       "      <td>121</td>\n",
       "      <td>9672.0</td>\n",
       "      <td>4</td>\n",
       "      <td>196.515484</td>\n",
       "      <td>251.734711</td>\n",
       "      <td>0.317532</td>\n",
       "      <td>2418.000000</td>\n",
       "      <td>0.780645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020_1001</td>\n",
       "      <td>6</td>\n",
       "      <td>8223</td>\n",
       "      <td>855191.97</td>\n",
       "      <td>5908</td>\n",
       "      <td>1277212.0</td>\n",
       "      <td>249</td>\n",
       "      <td>103.999996</td>\n",
       "      <td>144.751518</td>\n",
       "      <td>1.493480</td>\n",
       "      <td>5129.365462</td>\n",
       "      <td>0.718473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020_1002</td>\n",
       "      <td>6</td>\n",
       "      <td>819</td>\n",
       "      <td>180593.66</td>\n",
       "      <td>536</td>\n",
       "      <td>247584.0</td>\n",
       "      <td>32</td>\n",
       "      <td>220.505079</td>\n",
       "      <td>336.928470</td>\n",
       "      <td>1.370945</td>\n",
       "      <td>7737.000000</td>\n",
       "      <td>0.654457</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      广告投放渠道  月份  线索数量       广告成本  客户数量       订单金额  订单数量        线索成本  \\\n",
       "0  2020_1001   5  4736  416327.06  3386   204751.4    70   87.906896   \n",
       "2  2020_1002   5   155   30459.90   121     9672.0     4  196.515484   \n",
       "1  2020_1001   6  8223  855191.97  5908  1277212.0   249  103.999996   \n",
       "3  2020_1002   6   819  180593.66   536   247584.0    32  220.505079   \n",
       "\n",
       "         获客成本       ROI          客单价     线索转化率  \n",
       "0  122.955422  0.491804  2925.020000  0.714949  \n",
       "2  251.734711  0.317532  2418.000000  0.780645  \n",
       "1  144.751518  1.493480  5129.365462  0.718473  \n",
       "3  336.928470  1.370945  7737.000000  0.654457  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "#添加辅助列进行求和\n",
    "data_user_source.loc[:,'线索数量'] = 1\n",
    "data_add_wx.loc[:,'客户数量'] = 1\n",
    "data_user_source['月份'] = data_user_source['获取时间'].dt.month\n",
    "data_sourse_month = data_user_source.groupby(['广告投放渠道','月份']).agg({'线索数量':'sum'}).reset_index()\n",
    "data_user_month = data_add_wx.groupby(['广告投放渠道','月份']).agg({'客户数量':'sum'}).reset_index()\n",
    "\n",
    "#订单表进行月份聚合\n",
    "#订单总数量\n",
    "\n",
    "data_adv_order_count = data_orders.groupby(['广告投放渠道','月份'])['辅助列'].sum().rename('订单数量').reset_index()\n",
    "#订单销售总额\n",
    "data_orders['月份'] = data_orders['下单日期'].dt.month\n",
    "data_adv_OrderSum = data_orders.groupby(['广告投放渠道','月份']).agg({'订单金额':'sum'}).reset_index()\n",
    "#两个广告渠道各项对比\n",
    "data_cost = pd.merge(data_sourse_month,data_adv_cost,on = ['月份','广告投放渠道'])\n",
    "data_cost = pd.merge(data_cost ,data_user_month,on = ['月份','广告投放渠道'])\n",
    "data_cost = pd.merge(data_cost ,data_adv_OrderSum,on = ['月份','广告投放渠道'])\n",
    "data_cost = pd.merge(data_cost ,data_adv_order_count,on = ['月份','广告投放渠道'])\n",
    "data_cost['线索成本'] = data_cost['广告成本']/data_cost['线索数量']\n",
    "data_cost['获客成本'] = data_cost['广告成本']/data_cost['客户数量']\n",
    "data_cost['获客成本'] = data_cost['广告成本']/data_cost['客户数量']\n",
    "data_cost['ROI'] = data_cost['订单金额']/data_cost['广告成本']\n",
    "\n",
    "data_cost['客单价'] = data_cost['订单金额']/data_cost['订单数量']\n",
    "data_cost['线索转化率'] = data_cost['客户数量']/data_cost['线索数量']\n",
    "\n",
    "data_cost.sort_values(by = '月份')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.020530\n",
       "1    0.037089\n",
       "dtype: float64"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#提取出月份查看七八月的成交率\n",
    "data_order_one['下单日期']=data_order_one['下单日期'].apply(pd.to_datetime,format='%Y-%m-%d')\n",
    "data_order_one['月份'] = data_order_one['下单日期'].dt.month\n",
    "data_order_OneSum = data_order_one.groupby('月份').size().rename('新单数量').reset_index()\n",
    "data_NumFans = data_cost.groupby('月份')['客户数量'].sum().reset_index()\n",
    "data_order_OneSum['新单数量']/data_NumFans['客户数量']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.040541\n",
       "1    0.145907\n",
       "dtype: float64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查看七八月分大单占比\n",
    "data_BigOrder = data_orders.loc[data_orders['订单金额']>=10000,:].groupby('月份')['辅助列'].count().rename('大单数量').reset_index()\n",
    "data_AllOrder = data_cost.groupby('月份')['订单数量'].sum().reset_index()\n",
    "data_BigOrder['大单数量'] / data_AllOrder['订单数量']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "     * 可能由于是新项目的原因，八月对比七月有较大增长\n",
    "     * 2020_1001 比2020_1002的广告渠道表现得要好,2020_1001是主要的获客渠道\n",
    "     * 2020_1002获取客户的成本太高,8月份的转化率有大幅度降低不太稳定\n",
    "     * 八月环比七月的客户开发率有不错的增长,大单占比数量有较大的增长是ROI大幅度增长的主要原因\n",
    "     "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 查看收到赠送酒的客户后续销售情况"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "赠送客户一瓶酒后续的第一次购买成交率： 0.03787878787878788\n"
     ]
    }
   ],
   "source": [
    "data_active_order.columns =['客户_id','活动金额','赠送日期']\n",
    "data_active = pd.merge(data_active_order,data_orders)\n",
    "print('赠送客户一瓶酒后续的第一次购买成交率：',len(data_active[data_active.duplicated('客户_id')]) / len(data_active_order))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "活动赠送一瓶酒给意向客户的复购率: 0.8\n",
      "第一次购买的客户人数: 5\n",
      "复购客户人数： 4\n"
     ]
    }
   ],
   "source": [
    "active_purchase = data_active.groupby('客户_id')['辅助列'].sum().rename('下单次数').reset_index()\n",
    "print('活动赠送一瓶酒给意向客户的复购率:', len(active_purchase.loc[active_purchase['下单次数'] >= 2,:]) \n",
    "      / len(data_active[data_active.duplicated('客户_id')]))\n",
    "print('第一次购买的客户人数:',len(data_active[data_active.duplicated('客户_id')]))\n",
    "print('复购客户人数：', len(active_purchase.loc[active_purchase['下单次数'] >= 2,:]))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "  * 对于意向客户未够买产品，选择赠送一瓶给客户后续的成交率没有明显提升，复购率很高可能是数据量太少y存在些许偶然因素"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "  # 总结\n",
    "  #### 有部分没有打通电话的客户只跟进了一到两次，根据数据来看最好是要跟进三次，客户一直未接通可以尝试发短信，减少获客成本\n",
    "  #### 销售金额大于一万的单数占了销售总额的一半，应该重点关注下单金额大于一万的客户\n",
    "  ####  2020_1002广告渠道获取客户的成本太高，是2020_1001的两倍以上\n",
    "  #### 从六月份销售数据环比五月份销售数据来看，正处于较高的增长期，各环节都有很大的提升空间\n",
    "  ####  客户开发率目前还比较低，大于一万单的销售额是ROI较大增长的主要原因"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
